Anytime anyone runs any command on a SQL server, it changes the conditions of that SQL server. Execution plans get created, different objects are stored in memory, etc. I don’t like to use time as a performance tuning metric (I’m a big fan of logical reads), but most people do – especially executives, so when measuring execution time, it’s important to compare apples to apples.
Below is a script, which I call “Almost a Reboot” as it’s almost like rebooting server, that I run on a server anytime I’m tesing performance. I got the initial script from Kimberly Tripp (Blog/Twitter) at DevConnections in about 2004 – I’ve tweaked it when SQL2005 came out and it’s probably due for a few changes for SQL2008, so don’t be shy to offer suggestions.
My steps for performance tuning any SQL code are:
Run this script
Run the original code
Record the results
Make my change
Run this script again
Run the changed code
Record/Compare the results
--Almost a reboot--
DECLARE @DBName Varchar(255)
Set @DBName = <<SET DBNAME HERE>> --Fill this in to the database you want to flush
CHECKPOINT --writes dirty pages to disk
DBCC FREEPROCCACHE --clears entire plan cache
DBCC DROPCLEANBUFFERS --clears all clean data cache pages
DECLARE @intDBID INTEGER
SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = @DBName)
--Recompile the stored procedures
DBCC FLUSHPROCINDB (@intDBID) --clears all clean plan cache for specified database
Again, let me know if you have any good tips to update this.
His trick, which I’ve tried, is to change the batch separator in a co-workers SSMS to “Select”, as shown below – it doesn’t take effect until the next connection and it will drive them crazy:
Note for this tip, they will still get the same error:
The DATEDIFF function is very simple and very powerful…
With it you can:
Count the days, weeks, years, since an event occurred (I’ve done this to impress my kids by telling them how many day old they are)
Select DATEDIFF(D, ‘1/1’11’, GETDATE()) –How many days since New Years Day 2011
Count the minutes, seconds, milliseconds, microseconds, nanoseconds until something occurs (I’ve counted down until Friday at 5 PM a few times as well as to the day I retire).
Select DATEDIFF(D, GETDATE(), ‘1/1’15’) –How many days until the Coach Kill leads the Gopher Nation to the Rose Bowl
Almost a year ago, we had a major database migration issue, actually it was one issue that caused another issue that cause performance issues – nevermind the specifics, it was complicated. The bottom line is that I knew I could fix our SQL server issues, so for a couple of weeks, I was deep, deep, deep into not only SQL issues, but presenting the issues to management, other developers – technical and non-technical, my boss’s boss’s boss was hanging on every word I’d say. In fact, the days were so full of talking, I ended up doing most of the technical work in the evenings after the kids were in bed (it’s been a re-occurring theme/frustration for me).
After the issues were solved, which I merely assisted in solving, but I ran a lot of the meetings – there were a lot of others who stepped up and really impressed me with there determination – I wrote some notes on what I’d learned from those weeks. Here’s the list I came up with:
Five Technical tips that should be kept in the back of your mind when troubleshooting MAJOR issues to avoid the HACK title:
Temp tables vs table variables – be consistent, I didn’t think the code would last, so we had scripts with Temp tables and table variables – HACK!.
Insert statement – use full insert statements when using Excel or a SQL Script to create a script. There is a 1000 row limit to using the insert …. values (…), (…), etc.
Instantiate variables – especially if you are using them in loops
Update stats as a performance boost
Check missing indexes
Four Non-Technical Issues to be kept in the back of your mind when troubleshooting MAJOR issues to avoid the Hack title:
Stay focused on the issues.
Project your screen so people can see your logic – you can be the show and they will learn from watching you – at least they will learn that you are smart!
Know the data don’t do extra work just to feel good – don’t waste time checking everything, at times there needs to be the leap of faith that things will work – if there are issues, baby steps is a good method for troubleshooting.
Sometimes quick hacks turn into permanent/repeatable fixes, keep TSQL clean/consistent/use best practices even in your quick fixes – you never know.
That last one was really in my notes – unedited! I said I was going to post a blog about it, but haven’t until now.
So, keep in mind, your quick hacks may turn into permanent fixes, therefore consistently follow best practices and you will avoid the HACK title!
Add extra protection to your databases by adding a DDL trigger that will prevent users from deleting your databases.
Why?
In the last year, five times a user deleted the wrong database by mistake in SQL Server Management Studio – each time they thought they were connected to their local machine, but they were really connect to a shared development environment. If we would have had these triggers on our shared environments, we wouldn’t have wasted anytime restoring the databases.
Here’s a great article from Chriss K on MSDNwith code showing how easy it is to add this extra layer of protection.
3:30-4:45 : The Creepy DBA, How to Stalk Your Users – Jason Strate
4:45-5:00 : Closing comments/prize giveaways
Presentation:
The Creepy DBA, How to Stalk Your Users
Jason Strate, Digineer
Do you know when your users are using the production login accounts from their workstations? Are you aware of changes being made to the development servers that may impact future deployments? Has the new Junior DBA start giving out access like candy? As DBAs, we are responsible for knowing and acting on all of this and much more. Fortunately, SQL Server provides a number of features that you can use to monitor and track user activity. In this session, we’ll look at these features and demonstrate how you can use them to the extent that your users find you creepy!
Jason Strate (B/T), Digineer Inc, is a DBA with over fifteen years of experience. He is a recipient of the Microsoft Most Valuable Professional (MVP) for SQL Server since July, 2009. His experience includes design and implementation of both OLTP and OLAP solutions as well as assessment and implementation of SQL Server environments for best practices, performance, and high availability solutions. Jason is a SQL Server MCITP and participated in the development of Certification exams for SQL Server 2008.
Often times I’ll need to count how many records are added to a table based on the date. To do this, I use the GROUP BY and the CONVERT function.
The idea is to convert the datetime to just a date, then group and count the data by the date.
The Set Up
Here’s an example I got from this post, slightly altered:
SET NOCOUNT ON
Go
Create Table #RandomDates (DateTest datetime)
Go
Declare @Counter int
Set @Counter = 0
While @Counter < 1000
Begin
Insert #RandomDates (DateTest)
Select Dateadd(D, (-1)* ROUND((RAND() * (10)),0), Getdate()) --Random Date 0 to 10 days old
Set @Counter = @Counter + 1
End
The Example
Select Convert(date, DateTest) as 'Date',
Count(Convert(date, DateTest)) as 'Count'
from #RandomDates
Group by Convert(date, DateTest)
Order by Convert(date, DateTest)
Here are my results – yours will differ as the randomness implies
