09Jan 2009 by Grant Fritchey 4 Comments

Something Wicked This Way Comes

T-SQL
I sure hope peoples thumbs are pricking. I found this on the Apress web site. I guess it's OK to talk about it now that the first draft is finished. The second draft only has one chapter left and I finished copy editing on Chapter 6 of 16 yesterday. In other words, I'm going to have a new book published soon. The original plan was for May, but I heard a rumor that it might come out in March.
Read More
07Jan 2009 by Grant Fritchey 1 Comment

New England Data Camp v.1.0

PASS, T-SQL
It's getting a lot closer to the 24th.  On Saturday, January 24th, the first ever New England Data Camp will launch. We've got a number of speakers registered. Aaron Bertrand and Andrew Novick are guys I've got a lot respect for. I've been to their presentations before and they've been consistently very good.  We've got a few guys I haven't heard of personally, Talbott Crowell, Ayad Shammout, Sunil Kadimdiwan, Igor Moochnick. I'm going to present on execution plans and multi-environment deployments using DBPro (updated from the PASS presentation).  The other presentations cover topics from using the Resource Governor on SQL Server 2008 to Defending SQL Server from Injection Attacks to Create better and more Useful Cubes. It's shaping up to be an actual event. If you're in the neighborhood (New…
Read More
07Jan 2009 by Grant Fritchey No Comments

Table Valued Functions

T-SQL
I've been blogging for a whole nine months now. I decided to look back and see what's bringing people to the site. The number one search phrase is "sql server 2005 service pack 3" but the overwhelming topic that most people are using to get to the site are user defined functions, specifically multi-statement table valued user defined functions. It's completely understandable. Ever since I first saw these things in use back in SQL Server 2000, I thought they were slick. Unfortunately appearances can be deceiving. The reason so many people are searching out information on these things is because they just don't work very well. SQL Server can't create statistics on the tables generated through the multi-statement UDF. Because it has no statistics to work with, the query optimizer…
Read More
10Dec 2008 by Grant Fritchey 11 Comments

Whine

T-SQL
I'm working on the chapter on cursors. My editors thought my plan for the chapter was less than optimal. I wanted to have the chapter head Cursor Cost Analysis And then this would be the entire chapter: They cost too much. DO NOT USE THEM! Ah well. Guess I have to go through it and create all the silly examples. End of whine.
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
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 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