SQL Saturday #8 Orlando - October 25, 2008

I'll be presenting two sessions at SQL Saturday in Orlando next month.  Virtualizing SQL Server, and Monitoring SQL Server 2008.  Don't know what SQL Saturday is?  Find out more on the SQL Saturday website:

SQLSaturday #8 - Orlando 2008

There will also be an upcoming SQL Saturday event in Tampa at the beginning of 2009 hosted by the Tampa SQL Users Group, of which I am a member.  You can monitor this event as it  grows at the following site as well:

SQLSaturday #10 - Tampa 2009

Hope to see you there.  Also if there are any specific questions regarding one of the two topics above, send me a message, and I will try to cover that as a part of the presentation.

Monitoring the Plan Cache

If you run SQL Server on a 64bit server, then something that you should be monitoring from time to time is the size of your procedure cache.  This is especially important if you have an application that issues adhoc/non-parameterized queries against the SQL Server.  Since the procedure cache is stored as a part of the BPool, it can starve your buffer cache for precious memory.  A simple query that can help monitor this is:

with plancache_cte as
(select single=sum(case usecounts when 1 then 1    else 0 end),
        singlesize=sum(case usecounts when 1 then cast(size_in_bytes as bigint)/1024 else 0 end),
        reused=sum(case usecounts when 1 then 0 else 1 end),
        reusedsize=sum(case usecounts when 1 then 0 else cast(size_in_bytes as bigint)/1024 end),
        total=count(usecounts),
        totalsize=sum(cast(size_in_bytes as bigint)/1024)
from sys.dm_exec_cached_plans)

select
'Single use plans (usecounts=1)'= single,
'Single use plans size KB (usecounts=1)'= singlesize,
'Re-used plans (usecounts>1)'= reused,
'Re-used plans size KB (usecounts>1)'= reusedsize,
're-use %'=cast(100.0*reused / total as dec(5,2)),
'total usecounts'=total,
'total cache size'=totalsize
from plancache_cte

The SQL Programability Team blogged a wonderful series of blog posts in January 2007, that cover this topic and explain exactly what is going on and how it was somewhat fixed in Service Pack 2 of SQL Server 2005

http://blogs.msdn.com/sqlprogrammability/archive/2007/01.aspx

Monitoring for Blocked Processes On SQL 2005 - Extended Version

To help better clarify how to do an end to end monitoring of blocked processes on SQL Server 2005 I am going to expand upon my previous blog posting.  To begin with, it is necessary to configure the blocked process threshold on the SQL Server so that it generates the blocked process report in a trace output:

sp_configure'show advanced options',1 ;
GO
RECONFIGURE;
GO
sp_configure'blocked process threshold',5 ;
GO
RECONFIGURE;
GO

I chose the value 5 because if something is blocked for more than 5 seconds then that is a significant delay in performance and it is the lowest value you can set.  Once this has been done, a single event trace can be created to run on the server that will log the blocked process reports that get generated.  The below script will create this trace:

/****************************************************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 09/24/2008 11:12:03 AM */
/****************************************************/
 
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50 
exec @rc = sp_trace_create @TraceID output, 2, N'c:\BlockedProcessTrace', @maxfilesize, NULL 
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 13, @on
 
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error: 
select ErrorCode=@rc
finish: 
go

Make note of the TraceID that is output from running the above script.  Also not every SQL Server Service account can write to the root of the C drive.  None of my production servers can, but I am building this demo on my laptop where it can.  Make sure that a correct path is specified for the trace file.  Most servers will output traceid = 2 for this since there is the default trace running in SQL Server 2005.  Keep this number because it is needed to stop and delete the trace later on.

Now that the trace is active, to test it and demonstrate the blocked process report output open a new SSMS query window and run the following script:

use tempdb
create table temp1
(rowid int)
insert into temp1 values (1)

Then open another new query window and run the following script:

begin tran
update temp1
set rowid = rowid + 1

This will leave an open blocking transaction against the temp1 table in tempdb.  Now open another (my this is a lot of windows isn't it) query window and run the following script:

select * from temp1

Let it sit for about 10-20 seconds, and then kill the execution and close the query window.  Then rollback the transaction in the update query window and close that window as well (see I am cleaning up as we go).  The run the following query to clean up the rest of the example:

drop table temp1
--stop the trace
exec sp_trace_setstatus 2, 0
--delete the trace but leaves the file on the drive
exec sp_trace_setstatus 2, 2

Now to look at the output in the trace file run the following query:

select cast(TextData as xml), SPID, EndTime, Duration/1000/1000
from fn_trace_gettable(N'c:\BlockedProcessTrace.trc', default)
where eventclass = 137

By casting the TextData to an xml datatype you can click on it and have it open up formatted in SSMS.  The output will be similar to the following:

<blocked-process-report monitorLoop="160986">
  <blocked-process>
    <process id="process929d38" taskpriority="0" logused="0" waitresource="RID: 2:1:480:0" waittime="17953" ownerId="123106201" transactionname="SELECT" lasttranstarted="2008-09-24T16:39:38.620" XDES="0x9bc3920" lockMode="S" schedulerid="2" kpid="2064" status="suspended" spid="145" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2008-09-24T16:39:38.620" lastbatchcompleted="2008-09-24T16:39:10.667" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SQLDEMO" hostpid="4944" loginname="SQLDEMO\DemoUser" isolationlevel="read committed (2)" xactid="123106201" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
      <executionStack>
        <frame line="1" sqlhandle="0x0200000088baad31046d031f04c8e7293882ce42521d893f" />
      </executionStack>
      <inputbuf>
select * from temp1   </inputbuf>
    </process>
  </blocked-process>
  <blocking-process>
    <process status="sleeping" spid="78" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2008-09-24T16:39:37.060" lastbatchcompleted="2008-09-24T16:39:47.057" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SQLDEMO" hostpid="4944" loginname="SQLDEMO\DemoUser" isolationlevel="read committed (2)" xactid="123105638" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
      <executionStack />
      <inputbuf>
begin tran
update temp1
set rowid = rowid + 1

waitfor delay '00:00:10'   </inputbuf>
    </process>
  </blocking-process>
</blocked-process-report>

From this, you can see what is blocked as well as what is blocking.  Begin looking at the blocking process to determine why it is blocking.  If you can solve that problem then the issue goes away.

Hope it helps.

Beta SQL Server 2008 Exams Free Certification

I am a bit behind on this one, but I just learned that the SQL 2008 MCTS Developer exam is available for Beta and its free.  Read the following blog posting:

Register for Beta Exam 71-433: TS: Microsoft SQL Server 2008, Database Development - Beginning September 19, 2008

Register for Beta Exam 71-451: PRO: Microsoft SQL Server 2008, Designing and Optimizing Database Solutions – Beginning August 29th, 2008

I also added this blog to my blog reader so I am not behind on this kind of stuff in the future.  Free sounds good to me.

SQL Server 2008 Cumulative Update 1 Release

Following the new servicing model, SQL Server 2008 CU1 has been released by Microsoft:

http://support.microsoft.com/kb/956717/

New Virus targeting SQL Servers

A post on the forums about a job that existed that was questionable turned out to be a relatively new virus that is targetted at SQL Server.  If you follow best practice security implementation of SQL, then there is nothing to worry about.  You can read about this virus on the symantec website:

http://www.symantec.com/security_response/writeup.jsp?docid=2008-091215-0809-99

SQL Server Views and Performance

Someone asked me recently how do views affect performance in SQL Server, since they don't get a compiled execution plan like a stored procedure?  In investigating this, I was at first surprised to find that the person asking was absolutely correct, there is no compiled plan for a view in the dmv's:

select usecounts,cacheobjtype,objtype,query.text,executionplan.query_plan 
from sys.dm_exec_cached_plans 
cross apply sys.dm_exec_sql_text(plan_handle) as query
cross apply sys.dm_exec_query_plan(plan_handle) as executionplan 
where text not like '%sys%'and cacheobjtype ='compiled plan' 

If you run the above, you will find that Views get a Parse Tree instead.  So what does this mean exactly in the context of performance and using a view?  The answer is not really all that simple.  The Parse Tree defines the expansion of the view definition for execution time.  While the actual view doesn't have a compiled plan, the queries that use the view do.  You can test this by clearing the procedure cache:

DBCC FREEPROCCACHE

and then running a query, twice with different parameter values, with a view in your database:

SELECT * 
FROM [dbo].[SampleView]
WHERE OrderID = 10248
 
SELECT * 
FROM [dbo].[SampleView]
WHERE OrderID = 10250

If you look at the cache now, you should see that this query was parameterized by the SQL Server, and stored in the plan cache for reuse.  This means that your index tuning for a system that heavily uses Views that join multiple tables, must be based, not on the view definition itself but instead on the code that utilizes the view.

SQL Server Developer Factsheet

I love quick references, especially when they contain 90% of the common things that I have to double check in the BOL because I am not a walking desk reference on TSQL.  A really nice one that was provided in a post on the forums is available as a free pdf to download.  It is a very good reference that I would recommend printing and putting up on the wall of your desk/cube.

Download the SQL Server factsheet

Where did professional decency go? Especially amongst what is supposed to be some of our top minds?

I am not sure the chain of events that lead me to a blog that referenced this post on the Google groups, but it really is a sad thing when I read it:

Really Sad Post

When I started answering posts on the Forums earlier this year, I almost fell into this kind of diatribe once.  It really bothers me to see that MVP's would go about trashing one another in this form publicly on a post where someone was seeking assistance.  The MSDN Forums are not exempt from this kind of behavior, but they are monitored by Moderators and Answerers and problems are headed off early by removing inflammatory posts, or locking the thread.  It is really easy to become overbearing and arrogant and today it seems to be becoming more prevalent. 

One of the things I have tried to do the more I have learned, and as I have gained recognition in the SQL Server community at large is to remain humble and try to be a mentor rather than a critic.  There once was a time when I didn't know that null values added with non-null values would result in a null value.  If you happen to actually read my blog, then I implore you to keep in mind that there was once a time when you didn't know how to do the basics either.  If you choose to answer a question online, remember it was your choice, and the person is looking for help, not condescension or criticism, and if all you have to offer is one of the two, then please exercise the choose not to answer.  It is really that simple.

If you do answer, remember that in most cases, the Internet is forever.  Stuff never disappears completely, so formulate your answer in a manner that if someone else finds it, it is general enough or detailed enough that they get the answer too.  I have a number of posts that I wrote over the last year, that I now use as simple reference posts.  I also have posts marked that others wrote that were excellent write-ups and instead of rebuilding the answer, I'll post a reference to what already exists.

Now that I have that all said, I'll get off my soap box and go back to the regularly scheduled programming.

DayofWeek functions differently between SQL Servers?

A question this week on the forums that might be of interest involved a stored procedure having different results for the following query:

SELECT GETDATE() 'Today'
,convert(char(1),DATEPART(weekday, GETDATE()+6)) AS 'InDay_No'
,DATENAME(DW, GETDATE()+6) AS 'InDay_Name'
,convert(char(1),DATEPART(weekday, GETDATE()+2)) AS 'OutDay_No'
,DATENAME(DW, GETDATE()+2) AS 'OutDay_Name'

On one server the output for the day number was 2 for Tuesday while on another server it was 3.  This was caused by different default language settings for the login on the servers.  The @@Datefirst parameter will show the difference in configuration, and changing the default language resolves the problem.

SQL Server 2008 Extended Events Manager Build 1.0.1.89

I moved the project from the http://code.msdn.microsoft.com/ExtendedEventManager site to http://www.codeplex.com/ExtendedEventManager/ since this was the release point defined in the SQL Heros contest and I had originally planned to enter this into that contest when I realized that it was running through Sept 1, 2008.

Included in this release is an updated Documentation file, and fixes to all bugs that have been found to date.  This should be a very stable release, and pending any major problems will be the last release until I have the StandAlone library completed and coded with Unit Tests for use with PowerShell and or inside other applications.

This has definately been a long learning experience building.