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
02Dec 2008 by Grant Fritchey 6 Comments

More Dynamic Management Views: sys.dm_tran_locks

SQL Server, T-SQL
I'm working on the chapter on blocking in the new book. Explaining blocking of course means explaining locks. Prior to 2005, to understand locks, you went to sp_lock. Not anymore. Now you can query sys.dm_tran_locks. It's so much more sophisticated than the old system procedure.  Best of all, the information within it is simply a view into the internal locking infrastructure, so you're not placing extra load or extra processing on the system to marshal this data. A simple query to get basic locking information would look like this:   SELECT tl.request_session_id             ,tl.resource_database_id             ,tl.resource_associated_entity_id             ,tl.resource_type             ,tl.resource_description             ,tl.request_mode             ,tl.request_status   FROM sys.dm_tran_locks tl That just outputs roughly the same information as sp_lock. Lots more detail, not available in sp_lock, is available if you need it. Things…
Read More
30Oct 2008 by Grant Fritchey No Comments

Procedure Cache and Dynamic Management Views

SQL Server, T-SQL
I'm just going through the chapter on the procedure cache in the new book and I'm having a blast playing with the dynamic management views and functions that let you access the procedure cache. It's just too much fun and way too easy to get detailed information about the queries in the system, not like the old days. First, you can access the cache itself with the DMV, sys.dm_exec_cached_plans. This shows some of the data describing the plan in cache, but most importantly it provides the plan_handle. You need this for other joins later. You can also use sys.dm_exec_query_stats to get aggregated performance statistics about the plan. It also has the plan_handle and two things new to SQL Server 2008, the query_hash and the query_plan_hash, also known as query fingerprints.…
Read More
22Oct 2008 by Grant Fritchey No Comments

SQL Server 2005 SP3

SQL Server
It looks like it's on schedule for an RSN release (Real Soon Now). For those who can't wait, the latest cumulative update, #10, is available for SQL Server 2005. If you don't know, the cumulative updates are a collection of all the small hot fixes and patches that have been issued by Microsoft over a period of time. Instead of tracking them all down individually, you can get the cumulative update and install it.
Read More
29Sep 2008 by Grant Fritchey No Comments

Dissecting SQL Server Execution Plans at PDC

PASS, SQL Server, T-SQL
I just got word that Red Gate has printed more copies of the book that they'll be distributing at the Microsoft Professional Developers Conference that's taking place in LA. I just wish we had a 2008 version of the book now because, while most of it is still applicable, there's more that can be done with execution plans now. Have I mentioned I think the missing index information that's displayed with the statement text in the graphical execution plan in SQL Server 2008 is pretty slick? Well it is. I hope they're going to distribute it at the PASS Summit this year too. I think they are, but I don't know that for a fact.
Read More