26Feb 2009 by Grant Fritchey No Comments

Shrinking Databases

SQL Server
Tim Ford put up a hilarious blog post outlining an instance where shrinking databases is acceptable. I'm pretty sure the development method he describes is well documented and in use in more than one location around the world, including somewhere in my company. Read it.
Read More
25Feb 2009 by Grant Fritchey No Comments

Three Kinds of Execution Plans

SQL Server, T-SQL
You read that correctly, three kinds of execution plans. You may have thought that all you had to deal with are estimated and actual, but there is one more. The estimated plan is the plan that comes out of the optimizer. It's based on statistics and indexes and known objects within the system. The actual plan is the plan that was used to execute the query and will show all the actual number of rows processed, etc. It might be different than the estimated plan because the stastics were off or for any number of other reasons. Those were the ones you knew about. There is also the plan that gets stored in the plan cache, the compiled plan. I lied. The compiled plan and the estimated plan are the…
Read More
18Feb 2009 by Grant Fritchey 24 Comments

Refining the Missing Index Data

SQL Server, T-SQL
In my previous post I showed how you could determine if a query had missing index information, but it was difficult to get meaningful data out because I didn't know XQuery well enough. I spent some more time refining the query and here are the results. This new query will show some basic performance information gathered from the aggregate data in sys.dm_exec_query_stats. It combines this with the full data pulled from the Missing Indexes element in the XML of the execution plan. I've got it listing all the recommended columns and grouping. So this means that the performance data is repeated in order to allow for the full listing of groups & columns. It seems to work well. A couple of interesting points. My purpose is to provide a short-cut…
Read More
12Feb 2009 by Grant Fritchey 11 Comments

Missing Index Information and Query Stats

SQL Server, T-SQL
So the goal was to find a way to pull information from sys.dm_exec_query_stats so that we could identify poor performing procedures that were in cache at the moment and combine it with missing index information from sys.dm_db_missing_index_details. We're combining these because we're working with a Microsoft Dynamics CRM database that is almost all ad hoc queries and lots of them are against tables with missing indexes. The hope was to identify necessary indexes merely by looking at the longest running queries. Unfortunately there is no way to combine data from the missing indexes set of DMV's and all the execution DMV's that show query stats, execution plan, etc. None of the missing index tables has a plan handle or a plan hash column that would allow you to combine that…
Read More
10Feb 2009 by Grant Fritchey No Comments

Silly SQL Server 2008 Management Studio Trick

SQL Server
This one is pretty minor and came up in the forums at SQL Server Central. You may have noticed that the Registered Servers window is tabbed with the Object Explorer window. In SQL Server 2005, they were in the same window, stacked on top of each other. To get back to the "good old days" configuration, you can right click on the Registered Servers tab and select Floating. This will cause the window to detach and appear somewhere on your screen. Right click on it again and select Dockable. Now drag it on to the Object Explorer window and it should settle in on top. Ta da, back with the old GUI.
Read More
26Jan 2009 by Grant Fritchey 14 Comments

New England Data Camp v1.0 Results

Misc, PASS, SQL Server
I believe that the very first New England Data Camp was a success. We had about 185 attendees. There 18 sessions from 16 speakers. Both the sessions I gave and the one I sat in on were full. Credit goes to to Adam Machanic who did 90% of the work pulling this together. Amazing job Adam. My personal thanks to our sponsors.  First, Microsoft, who provided us with a magnificent facility, nice swag, a full AV suite, coffee and donuts and in the morning, and a lot of help. It wouldn't have come out as well as it did without you guys. Next, the Professional Association of SQL Server Users (PASS), who supplied us with money, without which we could not have eaten lunch, a few posters to decorate the…
Read More
13Jan 2009 by Grant Fritchey No Comments

Red Gate Crib Sheet Compendium

SQL Server, T-SQL
Red Gate has compiled a bunch of it's Cribsheets into a single E-book, the SQL Server Cribsheet Compendium. It's pretty cool. I've got two entries in there, performance tuning and backups & restores, along with great articles from Robyn Page, Phil Factor, Robert Sheldon and Amirthalingam Prasanna, pretty heady company. It's worth a look.
Read More
18Dec 2008 by Grant Fritchey 15 Comments

Profiler Research

SQL Server
A question came up on SQL Server Central as to why or how Profiler could bring down the production server.  I was aware of the fact that Profiler caused problems and that's why you should always use a server-side trace on production systems. Duh! Of course.... But why? I didn't have an answer. I just "knew" what everyone told me. So I set out to do some research. Initially I hit my favorite source of information on SQL Server (and it should be yours too) the Books Online. Here is what I was reading on the architecture of Profiler. Good, but not enough. I still want more information, so I've gone out a'wanderin' through the interwebs. First, Linchi Shea has this great set of tests comparing Profiler to server side…
Read More
03Dec 2008 by Grant Fritchey 3 Comments

Dynamic Management Views Put to Work on Blocking

SQL Server, T-SQL
This is my first pass at a modern (2005/2008) blocking monitoring script. I think it's a decent blocking script to capture information about blocks as they are occurring. Filters can be applied and it wouldn't be hard at all to add on other information such as execution plans, plan hash, etc. SELECT  tl.request_session_id AS WaitingSessionID        ,wt.blocking_session_id AS BlockingSessionID        ,wt.resource_description        ,wt.wait_type        ,wt.wait_duration_ms        ,DB_NAME(tl.resource_database_id) AS DatabaseName        ,tl.resource_associated_entity_id AS WaitingAssociatedEntity        ,tl.resource_type AS WaitingResourceType        ,tl.request_type AS WaitingRequestType        ,wrt.[text] AS WaitingTSql        ,btl.request_type BlockingRequestType        ,brt.[text] AS BlockingTsql FROM    sys.dm_tran_locks tl         JOIN sys.dm_os_waiting_tasks wt         ON tl.lock_owner_address = wt.resource_address         JOIN sys.dm_exec_requests wr         ON wr.session_id = tl.request_session_id         CROSS APPLY sys.dm_exec_sql_text(wr.sql_handle) AS wrt         LEFT JOIN sys.dm_exec_requests br         ON br.session_id = wt.blocking_session_id         OUTER APPLY…
Read More