I was researching some performance issues today on some SQL Server 2000 machines that were experiencing high CPU spikes and I noticed some strange cursors being called on one of the referenced servers.  The funny thing was that none of the queries were explicitly creating the cursor themselves.  How is this possible, you may ask yourself?  Well, the two servers were communicating with one another via the 4 part naming conventions(  Server.database.dbo.tablename )  via a SQLOLEDB configured linked server.  SQL Server was then creating server side cursors to pass the data back between to two servers.  In a profiler trace I saw numerous sp_cursorfetch calls and the CPU started going through the roof.  I also noticed that it seemed to be calling sp_cursorfetch for EACH ROW of the recordset.  As you can imagine if it is a large table then the number of roundtrips will be immense and in my case that’s exactly what was occurring.  To fix the problem I used OPENQuery to run the query statement on the remote server.  This allowed me to return the resultset once instead of having a roundtrip for each record.

Here is a sample of a server side cursor that would get created

DECLARE  @P1 INT
SET @P1 = 180150000
DECLARE  @P2 INT
SET @P2 = 8
DECLARE  @P3 INT
SET @P3 = 1
DECLARE  @P4 INT
SET @P4 = 109

EXEC Sp_cursoropen   @P1 OUTPUT ,
N'select name from sysdatabases
where name not in (''master'',''tempdb'',''model'',''pubs'')
and name not like ''%sqltech%''' ,
@P2 OUTPUT ,
@P3 OUTPUT ,
@P4 OUTPUT

SELECT @P1,@P2,@P3,@P4

 Then you will see numerous lines like the following:

exec sp_cursorfetch 180150000, 16, 1, 1

 When you execute the OPENQuery on the remote server this doesn’t occur.  It executes it once and returns the whole resultset to you.

Over the weekend I was running into a blocking issue with one of my clients.  I was using SQL Server 2008 and I was used to using the current activity pane in 2005 etc. to find the lead blocker. This is now gone in 2008 with the new Activity Monitor.  I started to use a lot of the old ways to diagnose using the profiler etc but this is a very tedious process.  I found this issue I was working on lastnight and it is resolved now but I did some searching on the web today and I found a really great article by Cherié Warren .  Cherie does a great job describing the ways to do it in SQL 2008 so I would advise you all to take a look when you get a chance.  She includes two really great scripts and I have included them below. 

To gather Lock stats run the following query:

SELECT  er.wait_time                      AS WaitMSQty
      , er.session_id                     AS CallingSpId
      , LEFT(nt_user_name, 30)            AS CallingUserName
      , LEFT(ces.program_name, 40)        AS CallingProgramName
      , er.blocking_session_id            AS BlockingSpId
      , DB_NAME(er.database_id)           AS DbName
      , CAST(csql.text AS varchar(255))   AS CallingSQL
      , clck.CallingResourceId
      , clck.CallingResourceType
      , clck.CallingRequestMode
      , CAST(bsql.text AS varchar(255))   AS BlockingSQL
      , blck.BlockingResourceType
      , blck.BlockingRequestMode
FROM    master.sys.dm_exec_requests er WITH (NOLOCK)
        JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
          ON er.session_id = ces.session_id
        CROSS APPLY fn_get_sql (er.sql_handle) csql
        JOIN (
-- Retrieve lock information for calling process, return only one record to
-- report information at the session level
              SELECT  cl.request_session_id                 AS CallingSpId
                    , MIN(cl.resource_associated_entity_id) AS CallingResourceId
                    , MIN(LEFT(cl.resource_type, 30))       AS CallingResourceType
                    , MIN(LEFT(cl.request_mode, 30))        AS CallingRequestMode
-- (i.e. schema, update, etc.)
              FROM    master.sys.dm_tran_locks cl WITH (nolock)
              WHERE   cl.request_status = 'WAIT' -- Status of the lock request = waiting
              GROUP BY cl.request_session_id
              ) AS clck
           ON er.session_id = clck.CallingSpid
         JOIN (
              -- Retrieve lock information for blocking process
              -- Only one record will be returned (one possibility, for instance,
              -- is for multiple row locks to occur)
              SELECT  bl.request_session_id            AS BlockingSpId
                    , bl.resource_associated_entity_id AS BlockingResourceId
                    , MIN(LEFT(bl.resource_type, 30))  AS BlockingResourceType
                    , MIN(LEFT(bl.request_mode, 30))   AS BlockingRequestMode
              FROM    master.sys.dm_tran_locks bl WITH (nolock)
              GROUP BY bl.request_session_id
                    , bl.resource_associated_entity_id
              ) AS blck
           ON er.blocking_session_id = blck.BlockingSpId
          AND clck.CallingResourceId = blck.BlockingResourceId
        JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
          ON er.blocking_session_id = ber.session_id
        CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE   ces.is_user_process = 1
        AND er.wait_time > 0

To review the objects run the following query:

DECLARE @SQL                           nvarchar(max)
      , @CallingResourceType           varchar(30)
      , @Objectname                    sysname
      , @DBName                        sysname
      , @resource_associated_entity_id int

-- TODO: Set the variables for the object you wish to look up

SET @SQL = N'
USE     ' + @DbName + N'
DECLARE @ObjectId int

SELECT  @ObjectId = CASE
                    WHEN @CallingResourceType = ''OBJECT''
                    THEN @resource_associated_entity_id
                    WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
                    THEN (SELECT  object_id
                          FROM    sys.partitions
                          WHERE   hobt_id = @resource_associated_entity_id)
                    WHEN @CallingResourceType = ''ALLOCATION_UNIT''
                    THEN (SELECT  CASE
                                     WHEN type IN (1, 3)
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions
                                           WHERE   hobt_id = allocation_unit_id)
                                     WHEN type = 2
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions
                                           WHERE   partition_id = allocation_unit_id)
                                     ELSE NULL
                                     END
                          FROM    sys.allocation_units
                          WHERE   allocation_unit_id = @resource_associated_entity_id)
                    ELSE NULL
                    END

SELECT  @ObjectName = OBJECT_NAME(@ObjectId)'

EXEC    dbo.sp_executeSQL
        @SQL
      , N'@CallingResourceType varchar(30)
      , @resource_associated_entity_id int
      , @ObjectName sysname OUTPUT'
      , @resource_associated_entity_id = @resource_associated_entity_id
      , @CallingResourceType = @CallingResourceType
      , @ObjectName = @ObjectName OUTPUT

Did you know that with SQL Server 2008 you can now clear the query plan for a specific statement you are testing?  This is a wonderful addition for SQL Server 2008 as you do not have to drop everything in cache anymore.  You can just drop the query you are working on.  Look at the following code below from Books Online (BOL) and try it out yourself to see how it works:

USE AdventureWorks;
GO
SELECT * FROM Person.Address;
GO
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM Person.Address%';
GO

plan_handle                                         text

--------------------------------------------------  -----------------------------

0x060006001ECA270EC0215D05000000000000000000000000  SELECT  * FROM Person.Address;

-- Remove the specific plan from the cache.
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);
GO

This is a webcast courtesy of SQLWorkshops.com 

Note: Doubleclick video for fullscreen.

In part 3 of this webcast Ramesh Meyyappan discusses some performance tips and tricks to use while rewriting queries and helps you to optimize query executions according to CXPACKET wait time and parallel query executions.

[mediaplayer src='http://sqltechconsulting.com/video/SQLWebcast2Part3of3.wmv' autoLoad=1 ]

This is a webcast courtesy of SQLWorkshops.com 

Note: Doubleclick video for fullscreen.

In part 2 of this webcast Ramesh Meyyappan discusses optimizing queries according to CXPACKET wait time and distribution of rowcounts per cpu core.

[mediaplayer src='http://sqltechconsulting.com/video/SQLWebcast2Part2of3.wmv' autoLoad=1 ]

This is a webcast courtesy of SQLWorkshops.com 

Note: Doubleclick video for fullscreen.

In part 1 of this webcast Ramesh Meyyappan discusses parallel query executions and CXPACKET waits.

[mediaplayer src='http://sqltechconsulting.com/video/SQLWebcast2Part1of3.wmv' autoLoad=1 ]

This is a webcast courtesy of SQLWorkshops.com 

Note: Doubleclick video for fullscreen.

In part 2 of this webcast Ramesh Meyyappan discusses how certain best practices do not always help performance.  Sometimes it even hurts.  One best practice he discusses is the practice of adding data files to tempdb according to the amount of cpu cores you have on the server.  He explains how optimizing memory spill overs of sort operations into tempdb makes more of an improvement and in some cases adding data files to tempdb actually hurts performance.
[mediaplayer src='http://sqltechconsulting.com/video/SQLWebcast1Part2of3.wmv' autoLoad=1 ]

This is a webcast courtesy of SQLWorkshops.com 

Note: Doubleclick video for fullscreen.

In part 1 of this webcast Ramesh Meyyappan discusses sql server query executions with sorts over tempdb vs sorts over memory and how you can modify queries for better performance.
[mediaplayer src='http://sqltechconsulting.com/video/SQLWebcast1Part1of3.wmv' autoLoad=1 ]