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
01Dec 2008 by Grant Fritchey No Comments

Finally, the GDR is Released

Visual Studio
From the Data Dude himself. This is great news. I've been working with the CTP's for several months now, telling the other DBA's on my team that they had to wait until it was completely ready for release. It's been a long wait, but I'm sure it's worth it. My congratulations to the team. I met several of you at the PASS Summit. I really apreciate the work you've put into this great tool. It really makes a difference in how we develop and deploy databases. The changes in the GDR are making a great utility even better.  Thanks Mr. Drapers. Thanks also to Jamie Laflen, especially for helping validate some of the ideas I presented at PASS. Thanks to all the rest of the team, whose names I don't…
Read More
25Nov 2008 by Grant Fritchey No Comments

XML to Multiple Queries

T-SQL
One of our development teams created a set of queries that are receiving some rather large XML parameters for processing. Unfortunately, the developers didn't think things through entirely. They passed the same, large, XML string in to the server five times in a row. Needless to say, the performance was substandard since the XML parser had to be instantiated five times. My initial suggestion was to use a wrapper procedure to perform a single load of the XML data and then call the other 5 procedures. I further suggested to load the XML into a temporary table and use that within the other procs. Unfortunately this is all taking place within SQL Server 2000. When my initial set of recommendations was done, we had solved one problem and introduced another. We were getting serious recompiles. If…
Read More
21Nov 2008 by Grant Fritchey 15 Comments

PASS Summit 2008: Day 3

Uncategorized
Two speakers at the keynote. The first, some guy from Dell, whose name I didn't catch, presented a few slides on something. The second presenter was Dr. DeWitt. It was one of those moments where I realized just how stupid I was. This man was scary smart. Not only that, he was witty. He went over parallel databases, shared nothing servers, and related technologies. He drilled down and defined the problems around developing a database that does parallel data storage. It was amazing. I can't properly convey it (I'm flat out, not smart enough), but you should download that video and check it out. One of my favorite quotes from him was "Query Optimizers are fragile." Don't get him wrong. He praised the concept of optimizers, but he was pretty…
Read More
21Nov 2008 by Grant Fritchey No Comments

Multi-Statement Table Value Function Alternative

T-SQL
I was talking with Andrew Novick at the PASS Summit. We ended up talking about multi-statement table valued functions. I was talking about how much the performance of these things is weak (to be kind). He agreed, but suggested an alertnative that might be worth further exploration, if you really think you need multi-statement UDFs. Andrew said that in his testing, using CLR offered a great alernative to using the UDF. I'm still pretty convinced that any type of programming you're doing on the SQL Server end that requires a UDF or CLR is probably either just TSQL gone wrong (see Jeff Moden and the RBAR concept) or it's something that doesn't belong on the SQL Server but instead should be done on the application layer somewhere. Still, it is…
Read More
21Nov 2008 by Grant Fritchey No Comments

PASS Board

PASS
The winners were the other guy, Lynda Rab and Andy Warren. Congratulations to all three. I lobbied hard for one of the three winners, Andy, so my special congratulations go out to him. On the back of my laptop, as I type this, is the logo (probably due for a law-suit soon) of the SQLBatman. He lost his second PASS election in a row. However, he came through like a trooper and says he's going to run again next year (go Susan Lucci, uh, I mean Tom).
Read More
21Nov 2008 by Grant Fritchey 3 Comments

PASS Summit Day 2

PASS
I only half listened to the key notes. The party's the preceding night may have had something to do with it. First session was with Kalen Delaney on Plan Guides. She didn't really do plan guides though. Instead she talked about guiding plans. She is such a great presenter. I like her use of the language and precise definitions. Lubor Kollar (sp?) was in the room and made a point of standing up & addressing some of Kalen's info.  My best take away was that plan guides (she covered those too) do not reduce compile time and can in fact increase compile time. Second best was the use of plan guides as a mechanism for testing since you can apply the guide, enable & disable, without rewriting the proc. Sessions…
Read More
19Nov 2008 by Grant Fritchey 1 Comment

PASS Summit Day 1

PASS
What a day. I'm not going to do justice to the keynote. First off, the room was frigging huge (not Tech-Ed huge, but enormous for PASS). It was pretty full too. The keynote wasn't terribly exciting, but it was terribly interesting. Watching 150tb databases process queries in less than 30 seconds is not something you see every day. Of more interest to me was the info on the new deployment mechanisms across database "fabrics" coming up with the new version of SQL Server (yes, it's at least two years away, but it's only two years away). It pretty clearly incorporates some of VSDB, but it's also a bit different. It includes data for example. This will be something for me to keep an eye on. My first session was with Allen White.…
Read More