Tag Archives: T-SQL

Developers Make Your SQL Server Queries Go Faster!

Being that today is my birthday I thought I would share a nice gift to everyone who follows my blog. Below is a free link to a recorded video of one of my latest presentations that goes over multiple SQL Server developer anti-patterns I see repeated over and over again in the field. Most of these are patterns that any IT professionals can identify and fix just by identifying the pattern and changing the pattern to another one shown in the video that is optimizer friendly.  I promise no brain surgery is required 😉

Make Your SQL Server Queries Go Faster

I hope you enjoy the hour long video on making your SQL Server queries go faster!

Bonus Material

If you want to play along feel free to download the sample scripts.  If you want to learn more about being a proactive performance tuner check out my root cause performance analysis blog series which includes my What Is Running Script, Disk LatencyWait Stats monitor processes and more..

T-SQL Tuesday: What’s Currently Running?

My good friend Allen White is hosting this months installment of #TSQL2sDay so I am motivated to jump in. #TSQL2sDay is the creation of Adam Machanic. The concept is simple, about a week before the second Tuesday of the month a theme will be posted.  Any blogger that wishes to participate is invited to write a post on the chosen topic and any post that is related to both SQL Server and the theme is fair game.The challenge for this month’s T-SQL Tuesday is: What T-SQL tricks do you use today to make your job easier?

What’s Currently Running?

One of my favorite tricks is actually just a little script I have in my toolbox to find out what queries are currently running right now. In fact I have had quite a few people ask me the for this script so I am glad to share it in this blog post.  With SQL Server 2005 and above  SQL Server provides Database Management Views that give you direct access to executing requests and running process. The following query uses sys.dm_exec_request, sys.sysprocesses. We will also use cross apply to get the query text from sys.dm_exec_sql_text and the execution plan from sys.dm_exec_query_plan.

The Good Stuff…

{UPDATE: 1/1/2012 – Replaced sysprocesses with sys.dm_exec_sessions as recommended by Phil in the comments

UPDATE: 6/5/2013 – Changed CROSS APPLY’s to OUTER APPLY’s so we capture statements without execution plan or SQL Text.}

-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
[Spid] = sp.session_Id
,er.request_id
,er.command
,[Database] = DB_NAME(er.database_id)
,[User] = login_name
,er.blocking_session_id
,[Status] = er.status
,[Wait] = wait_type
,CAST('<?query --'+CHAR(13)+SUBSTRING(qt.text,
(er.statement_start_offset / 2)+1,     ((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)    ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)+CHAR(13)+'--?>' AS xml) as sql_statement
,[Parent Query] = qt.text
,p.query_plan
,er.cpu_time
, er.reads
, er.writes
, er.Logical_reads
, er.row_count
, Program = program_name
,Host_name
,start_time
FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions sp ON er.session_id = sp.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) p
WHERE sp.is_user_process = 1
/* sp.session_Id > 50
-- Ignore system spids. -- */
AND sp.session_Id NOT IN (@@SPID)
ORDER BY 1, 2

When was that object modified?

Yes, every once in a while when I am deploying an application I ask myself the following question.  What database objects (tables, stored procedures, functions etc..) did I modify with this release?  

Ideally this is documented in the release plan but I will admit I have been known to slip every once in a while.  Therefore, I am showcasing a query that can provide help.  This query was written by Gordon Bell and it can be found here.   It uses the sys.objects DMV that are included in SQL 2005 & 2008. 

I will defiantly throw this script into my bag of tricks. 

select name, modify_date,
case when type_desc = 'USER_TABLE' then 'Table'
when type_desc = 'SQL_STORED_PROCEDURE' then 'Stored Procedure'
when type_desc in ('SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION', 
'SQL_TABLE_VALUED_FUNCTION') then 'Function'
end as type_desc
from sys.objects
where type in ('U', 'P', 'FN', 'IF', 'TF')
and is_ms_shipped = 0
order by 2 desc

Converting a vertical table to horizontal table

Last week I received a request to convert a vertical table from a vendor application into a horizontal table.  There was one catch, the vertical table included text columns that needed to be pivoted horizontally.  The following was my plan for tackling this request.

  1. You must figure out how many columns are required for the worst case scenario in you horizontal table.  You can do this by multiplying the columns being pivoted by the rows in the vertical table. In this example you know the worst case is eight (see figure two.)  In this example we will assume that you will not know how many columns are needed.
  2. Now we will use dynamic sql to create our new table that will support the columns needed in the horizontal table.
  3. Next we will create a cursor that will loop through the vertical table to create and execute insert statements to populate the horizontal table.

Example of vertical table (Input)

ManagerName ManagerEmail Review Employee
Jack Wilson jwilson@comp.com 2009 Review John Sterrett
Jack Wilson jwilson@comp.com 2009 Review Bo Smith
Hank Reed hreed@comp.com 2008 Review John Sterrett
Jack Wilson jwilson@comp.com 2008 Review Chris Cupp

Figure 1 – The vertical table

The following is the horizontal table (output)

ManagerName ManagerEmail Review1 Employee1 Review2 Employee2 Review3 Employee3
Jack Wilson jwilson@comp.com 2009 Review John Sterrett 2009 Review Bo Smith 2008 Review John Sterrett
Hank Reed hreed@comp.com 2008 Review Chris Cupp NULL NULL NULL NULL

Figure 2 – The horizontal table

For this example we used two scripts.  The first script will create a vertical table and insert the sample data.  The second script populates the horizontal table and also prints out all scripts created dynamically to the message window.

If you have any questions please feel free to leave a comment . I will try to point you in the right direction.

Free SQL Server 2008 Training Kit

Are you looking for some free SQL Server training?  Are you looking to upgrade your SQL Skills to SQL Server 2008? If so, Microsoft has a download just for you.

Download SQL Server 2008 Developer Training Kit

The SQL Server 2008 Developer Training Kit includes presentations, sample code and labs that cover the following topics:

  • Spatial Support
  • FILESTREAM
  • CLR
  • Reporting Services
  • Date and Time
  • T-SQL enhancements including  Table Value Parameters, Merge, Row Constructors, Grouping Sets and more..

T-SQL Scripts included with Management Studio

Have you ever wanted to quickly script T-SQL code to add columns to a table, alter a partition function, create endpoints, configure data capture, create an indexed view, backup or restore databases?  These tasks and more are included as templates.  The template explorer is included in SQL Server 2005 and SQL Server 2008 Management Studio (SSMS).   To view the template explorer hit Ctrl+Alt+T or select Template Explorer from the view menu in SSMS.  You will find templates to create objects such as databases, tables, views, indexes, stored procedures, triggers, statistics, and functions. In addition, there are templates that help you to manage your server by creating extended properties, linked servers, logins, roles, users, and templates for Analysis Services, and SQL Server Compact 3.5 SP1.

image

Once you load a template in the editor section in SSMS you can specify values for the template parameters values.  Press CTRL+Shift+M to specify values as shown on the next figure.

image
The templates are placed in the users Documents and Settings folder under Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates. Templates are available for solutions, projects, and various types of code editors as they are scripted as individual sql files.