Performance Tuning

Should release cadence be slowed if you don’t have database load testing?

I got a question recently about a panel discussion on Database Development Disasters at SQL in the City Streamed. I had framed a question as, “how fast should development go without load or performance testing?” I got a follow-up question from my friend Chris Randvere at Redgate: he asked for more information about what the question meant? I realized that my wording had been pretty unclear. I had meant to ask the panelists what their thoughts were on release cadence when a team lacks tooling to do automated load and performance testing outside of production. Should the lack of automated performance testing ability change the rate at which we deploy software? In other words, if we can’t do performance and load testing, does that mean that we should or shouldn’t deploy a change to a database every weekday? I don’t think we covered this super-well in the panel because I…
Read More

How To: Automate Continuous SQLServer Activity with Stored Procedures and Powershell Jobs

The Goal It’s often useful to be able to run a bunch of stored procedures in the background over a period of time against a test instance. This can be nice for: Demos and presentations. Populating DMVs with data you can slice and dice. Learning to use things like extended events and server side trace (which are much more interesting with something to look at). Testing a variety of automation scripts. This post shows you how to create several stored procedures for AdventureWorks2008R2 which will provide different result sets and have slightly different run times when run with a variety of parameters– in this case, individual letters of the alphabet. You can then run PowerShell commands which start jobs in the background. Each job runs a stored procedure and loops through all letters of the alphabet, providing each one as a parameter. You can set the job to do that…
Read More

Be Prepared: Collecting Data from sp_WhoIsActive in a Table

I am a fangirl of Adam Machanic‘s free sp_whoisactive stored procedure. One of the great things about sp_whoisactive is that it’s very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect the data you want over an interval. What’s sp_WhoIsActive? Sp_whoisactive is a procedure written by Adam Machanic. It can be found at What to Do Today Do your future self a favor: Today, set yourself up the following: Create the sp_whoisactive stored procedure in a user database you use only for administration Play with the script I provide below and look at the  parameters of the stored procedure Save a script which creates a table for your preferred version of sp_whoisactive, then runs in a loop to store the data. Why Do This? Even if you’re already running monitoring tools, using sp_whoisactive to…
Read More

Date Rounding Tactics and the Tiny Devil of SMALLDATETIME

With every new year I think a little bit about time and dates. This posts looks a little more at that in TSQL. Rounding Dates: Which way is best? Sometimes in TSQL you need to round a datetime value to the precision of either a day, hour, minute, or second. I realized recently that I have a few ways I know how to do this, but I wasn’t sure which was the most efficient. I did a little searching and didn’t find anything super conclusive. I had a little chat with Jeremiah Peschka (blog | twitter) and he told me which way he thought was fastest and why. And so I decided to run some tests. Jeremiah has a way of being right about these things, but I had to see for myself. I’ll go ahead and tell you: He was totally right, and I’ll show you why. But I…
Read More

Filling in Data Potholes Redux: Tally Tables vs CTEs

In A Previous Installment Re-Ducks … our heroine (that’s me) rediscovered CTEs, specifically in the recursive style. That was in my post “Filling in Data Potholes with Recursive CTEs.” To recap: I was working on a problem with gaps in temporal data. The basic scenario was: Imagine that you are writing a script that looks at data grouped by the minute. You notice that there are no rows for some minutes, and you’d like to display a value when that is the case, probably showing a count of zero. For the particular problem I was looking at, I was using small datasets and generating a list of all the valid dates with a recursive CTE performed well for me. From the Comments The best thing about blogging is not really sharing what you know: it’s getting to learn more. You get to learn from the process of writing the blog,…
Read More

SQL PASS Day 1: To Free or Not To Free the Proc Cache?

Yesterday was day 1 of SQL PASS 2009. I am attending a variety of sessions on execution plans this year, and along the way I heard three very different opinions yesterday on managing the procedure cache in presentations. Rule of Thumb: The “it depends” answer is usually right. Opinion 1: Never Ever Clear the Proc Cache on a Production Server This first opinion came in a good, solid presentation on using execution plans for troubleshooting. There were some good examples of when you want sql to look at the statistics and trigger generating a new plan, and when you don’t. (AKA when parameter sniffing is a good or a bad thing.) But the speaker was wholeheartedly against clearing the proc cache in production. While I can definitely see this being true for some systems, I have definitely seen advantages of clearing the proc cache on others (more to come below),…
Read More