automation

Developer Seeks DBA Career (Dear SQL DBA, Episode 13)

A C# developer wants to be a DBA, but is having a hard time landing a job. Should they take a pay cut and go for a Junior level job? Or could there be another path? This is a “listen-able” 15 minute video. Find the podcast version at littlekendra.com/dearsqldba. A written version of the discussion with clickable links is just under this video. Dear SQL DBA, I’ve been a C# developer since year 2000. I want to move to be a DBA. I’ve started getting involved at user groups and SQL Saturdays but nobody wants to hire me as a DBA. I have been trying to move to other companies but my resume is strongly inclined to show my C#, front end experience. I know for a fact that I’m really good on SQL as I keep solving problems in every other project but no one seems to pay really attention to…
Read More

Sliding Window Table Partitioning: What to Decide Before You Automate

Before you do all the work to map out a complex sliding window table partitioning scheme for your SQL Server tables, here’s the top five questions I’d think through carefully: 1) Do You Need Table Partitioning? Some folks think they need partitioning for performance– but it really shines as a data management feature. Just because you’ve got tables with millions of rows in them doesn’t necessarily mean that partitioning will make queries faster. Make sure you’ve worked through traditional indexing and query re-writes first. Partitioning is lots of work, so don’t skip this question. Bonus: traditional indexes don’t require Enterprise Edition like partitioning. And even if you do eventually go down the partitioning route, you’re going to need to be familiar with the top queries that hit the table for tuning later, anyway. 2) Do You Need a Sliding Window? With “sliding window” table partitioning, you regularly add new boundary…
Read More
counting-indexes-dragon

How to Check if an Index Exists on a Table in SQL Server

It seems like this should be easy. But it’s not. Code Should be Rerunnable – So You Need to Check if Indexes Exist Whenever you set up a script to create or drop an index, you want a safety check in there. Drop the index if it exists. Create the index if it doesn’t. Or do something else programmatically. Checking if an index exists is a pretty frequent task. But there’s no simple function to test if an index exists in SQL Server. Here’s what I’ll show you in this post: Example code to check if an index exists using OBJECT_ID. The code is simpler, but it requires a shared schema lock on the table you’re checking. Example code to check if an index exists just using joins. This uses less locking, but is wordier code. Examples of why simpler code doesn’t do the trick. Why the DROP_EXISTING=ON clause in…
Read More

Automation: Granting Read Perms for Developers

Yeah, you heard me. “But Kendra, why would we want to grant developers read  permissions? And why would we automate  it? And at what point did you lose your mind?” Well, Virginia, there may or may not be a Santa Claus, but there are a lot of developers and good reasons to give them read access on many SQL Server instances. In my world, it’s every instance in the pre-production environment, which is a couple  hundred and growing. And it ain’t just read they’re needing, they should be empowered with all of: In master: View server state– so they can look at all those pesky spids they can block, and which may be causing problems they’re investigating.In msdb: db_datareader — so they can query things not easily seen through the GUI SQLAgentReaderRole — so they can look at currently executing jobs and history through the GUI In each user database:…
Read More
Menu