Notes from SQL Server Premier Field Engineer

SQL Server system information

I guess many of you are familiar with build in SQL server function @@version, which you can use to quickly tell what SKU of SQL Server and Windows OS you are using as well as build numbers. Perhaps you are also aware of  serverproperty(param) function which tells you similar things eg your SQL Server instance name, if it is clustered or not etc.

Simple query below uses serverproperty function to tell you system level info about SQL Server.

I build it just recently for a small pet project of mine and decided to post it here just in case someone finds it useful too :)  

select 

serverproperty('ProductVersion') as [SQL server build number],

serverproperty('Edition') as [SQL server SKU],

serverproperty('ProductLevel') as [SQL server service pack level],

serverproperty('IsClustered') as [Is SQL server clustered],

serverproperty('MachineName') as [Windows server name],

serverproperty('ServerName') as [Full SQL server instance name],

serverproperty('InstanceName') as [SQL server instance name],

serverproperty('ProcessID') as [SQL server Windows process ID],

serverproperty('Collation') as [SQL server instance collation] 

Embeding XML into XML using FOR XML option

Hello, everyone!

Today I am going to chat briefly about the question a co-worker asked me about.

The problem was with embedding one XML snippet generated by using FOR XML option of the query into another XML also generated using FOR XML option.

Here is cleaned up version of the query :

declare @t table(c1 int, c2 int, ts binary(8))

insert into @t(c1,c2)

select 1, 2 

union all

select 3, 4 

union all

select 5, 6

declare @message xml

select @message =

(  select      

123 as '@attribute1',

                345 as '@attribute2',

456 as '@timestamp',

                getdate() as '@currentDate',

                     (select c1 as '@c1',

                             c2 as '@c2'

                  from @t

                       for xml path('item')

                      ) as 'items'

           for xml path ('items_list'), binary base64

      )

select @message 

If you execute this query directly against your instance of SQL Server you will get the following result:

<items_list attribute1="123" attribute2="345" timestamp="456" currentDate="2010-01-29T19:43:36.117">

  <items>&lt;item c1="1" c2="2"/&gt;&lt;item c1="3" c2="4"/&gt;&lt;item c1="5" c2="6"/&gt;</items>

</items_list> 

Please notice &lt; and &gt; in the output, since '<' and '>' have specific meaning in XML as tags. This is surely not what my coworker expected and intended.

The reason for this is that the result of the inner FOR XML query is returned as nvarchar(max) by default. The straightforward way around it is to cast the result to xml type using CAST as follows:

declare @t table(c1 int, c2 int, ts binary(8))

insert into @t(c1,c2)

select 1, 2 

union all

select 3, 4 

union all

select 5, 6

declare @message xml

select @message =

(  select      

123 as '@attribute1',

                345 as '@attribute2',

 456 as '@timestamp',

                getdate() as '@currentDate',

                     cast (

(select c1 as '@c1',

                             c2 as '@c2'

                  from @t

                       for xml path('item') 

                      )

as xml ) as 'items'      

           for xml path ('items_list'), binary base64

      )

select @message 

The output of this is what was intended:

<items_list attribute1="123" attribute2="345" timestamp="456" currentDate="2010-01-29T19:51:06.940">

  <items>

    <item c1="1" c2="2" />

    <item c1="3" c2="4" />

    <item c1="5" c2="6" />

  </items>

</items_list>

 However, in SQL Server FOR XML clause allows for TYPE option to achieve exactly this so there is no need to cast the result to XML type:

 declare @t table(c1 int, c2 int, ts binary(8))

insert into @t(c1,c2)

select 1, 2 

union all

select 3, 4 

union all

select 5, 6

declare @message xml

select @message =

(  select      

123 as '@attribute1',

                345 as '@attribute2',

   456 as '@timestamp',

                getdate() as '@currentDate',

                     (select c1 as '@c1',

                             c2 as '@c2'

                  from @t

                       for xml path('item'), TYPE 

                      ) as 'items'

           for xml path ('items_list'), binary base64

      )

select @message

with much desired output:

 <items_list attribute1="123" attribute2="345" timestamp="456" currentDate="2010-01-29T19:55:37.973">

  <items>

    <item c1="1" c2="2" />

    <item c1="3" c2="4" />

    <item c1="5" c2="6" />

  </items>

</items_list>

Hope description of this small 'gotcha' was useful.

Until next time.

-Yuriy  

Maximum sizes and numbers for SQL Server

While working with SQL Server you've probably heard certain restrictions like 'There can be at most 1,024 columns declared in the table' or 'There can be only 8,600 bytes per row'. Some are urban legends and travel by the word of mouth others are hard to keep in the head even for people who constantly work with SQL Server. In case you wondered what these limits are, Microsoft has a complete list of them here . I personally think it is quite useful to be aware of these limits.

SQL Server 2005/2008 database tuning - picking low hanging fruits - part 2

Some time ago I wrote a post how SQL Server optimizer keep track of what indexes maybe beneficial and logs this info into sys.dm_db_missing_index_* DMVs. Brent Ozar kindly pointed out that one can script 'CREATE INDEX' statement to build missing indexes to achieve performance gains. However, what was missing in my past post is what SQL statement may benefit from such an index. Fortunately, SQL Server puts missing index information in query execution plan, which is just a typed XML, whose XSD schema file can be found in <disk>:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan folder of your SQL Server 2008 installation. One place to lookup for query execution plans in XML format for SQL statement is a plan cache of SQL Server using

select qp.query_plan,

SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 ,

(( CASE qs.statement_end_offset

WHEN -1 THEN datalength(st.TEXT)

ELSE qs.statement_end_offset

END

- qs.statement_start_offset)/2) + 1) AS txt

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(sql_handle) st

cross apply sys.dm_exec_query_plan(plan_handle) qp

Then one can modify above statment with XQuery to query for Missing Index information using above mentioned XSD schema for XML showplan in SQL Server 2008 to get what potential index is missing for the statement. Please note that not all statement have index missing, only some.   

Luckily, Brent Ozar and great site www.sqlserverpedia.com already have full query ready for this which I copied down below for your convenience.

SELECT qp.query_plan
, total_worker_time/execution_count AS AvgCPU
, total_elapsed_time/execution_count AS AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads
, execution_count
, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 , ((CASE qs.statement_end_offset WHEN -1 THEN datalength(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS txt
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]' , 'decimal(18,4)') * execution_count AS TotalImpact
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]' , 'varchar(100)') AS [DATABASE]
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]' , 'varchar(100)') AS [TABLE]
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
WHERE qp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex[@Database!="m"]') = 1
ORDER BY TotalImpact DESC

So, now you know what indexes are missing and also have an idea what SQL statements benefit from that index.

Happy querying!

 -Yuriy 

TSQL coding patterns I

With this post I will start writing some small TSQL coding patterns and tricks which make daily life of SQL Server database developer easier and are used a LOT when dealing with large databases.

Automatically refreshing your Excel reports

Many organization have lots of Excel reports and pivot tables whose data comes from SQL Server. As data on the backend database changes you would need to repopulate your excel books with updated information. Doing it by hand can be very tedious manual process for the reporting analyst and there are ways to automate it. The simplest way is to create a SQL Agent job which refreshes your books on a scheduled basis. VBScript below does exactly that. Just add a step to your refresh job for every report you want to refresh substituting "<path to your excel file>" with location of your excel file on some file share.

 Set oWorkBook = oExcel.Workbooks.Open("<path to your excel file>")

Now you saved yourself a whole lot time and can start working on more interesting things!

Cheers, -Yuriy

Script below

Function Main()
Dim oExcel
Dim oWorkBook
Dim oWorksheet
Dim oPivotTable
Dim IStartedExcel
 
On Error Resume Next
Set oExcel = GetObject("Excel.Application")       'will attach to an existing instance if it is there.
oExcel.Quit
 
'Instantiate excel object; disable alert messages
Set  oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = false
 
'when debugging set visible = true
oExcel.visible = true

'open workbook we wish to refresh
Set oWorkBook = oExcel.Workbooks.Open("<path to your excel file>")
oWorkbook.EnableConnections
oWorkbook.RefreshAll
oExcel.CalculateUntilAsyncQueriesDone
oWorkbook.Save
oWorkBook.Close
 
oExcel.Quit
Set oExcel = Nothing
 
'Main = DTSTaskExecResult_Success

End Function

Interpreting SQL Server execution plans

As many of you may know, most of the time performance problems with SQL server come from bad queries/execution plans. That is why it is important to know how to interpret them. Most of you are likely peeked on graphical execution plans in SQL Server once or twice and familiar with physical operators for index seeks, table scans and joins but there are much more operators. In case you ever wondered or looked for a complete list of operators SQL Server implements here it is from deep inside of SQL Server documentation - books online. I personally find it very handy as it helps me to understand what SQL server does to return results and how to improve my query... 

 Hope you find it useful too

Indexing fact tables

Recently I was working on datawarehouse project during which i had to automate generation of indexes for fact tables. Tuning SQL Server relational warehouse is a complex task. As you may already know fact tables have lots of foreign keys into dimension tables in star schema in relational warehouse.  These foreign keys should be your first candidates for indexes as most of the queries in a relational warehouse are joins on a surrogate keys of fact tables with dimensional tables.

Script below will help you to automate generation of indexes on fact tables from foreign keys. Just change <fact table name> to an actual fact table name.

Hope it saves you some time in tuning your datawarehouse 

select 'if exists (select name from sys.indexes where name=''IX_<fact table name>_'+c.name+

''') drop index <fact table name>.IX_<fact table name>_'+c.name

from sys.foreign_key_columns fkc

inner join sys.foreign_keys fk on fk.object_id=fkc.constraint_object_id

inner join sys.columns c on c.column_id=fkc.parent_column_id

where fk.parent_object_id=object_id('<fact table name>')

and c.object_id=object_id('<fact table name>)

select 'create nonclustered index IX_<fact table name>_'+c.name+ ' on <fact table name>('+c.name+')' from sys.foreign_key_columns fkc

inner join sys.foreign_keys fk on fk.object_id=fkc.constraint_object_id

inner join sys.columns c on c.column_id=fkc.parent_column_id

where fk.parent_object_id=object_id('<fact table name>')

and c.object_id=object_id('<fact table name>'')

Enjoy it and until next time...

SQL Server 2008 failover clustering whitepaper

Microsoft SQL Server 2008 Virtual Labs

Hi guys!

While travelling around Australia and helping Microsoft customers to get work done with SQL Server, I was quite surprized to realize how many customers do not know about Microsoft SQL Server 2008 Virtual Labs . There you can remotely test drive latest and greatest innovations in Microsoft data platform without leaving your own desk (some registraton may be requird though). How cool is that?

Give it a try, fellows, and enjoy!

-Yuriy

SQL Server 2008 SP1 is out

SQL Server 2005/2008 database tuning - picking low hanging fruits - part I

Hello fellow readers!

It has been a while since I posted. Sorry about that - have been busy travelling around customers in Australia.

Let me write a few words how you can easily increase performance of your database application without doing much hard work. Sounds cheezy you might think. Not at all. I would like to cover two easy options you have to collect those low hanging fruits of optimizing SQL Server performance. First shows the power of dynamic management views (DMVs) and second uses tools which have been shipping with SQL Server for a long time - Database Tuning Advisor and SQL Profiler. Let's cover these approaches one at a time.

1. Dynamic management views

SQL Server 2005/2008 has a set of dynamic management views maintained by query optimizer which tell you if there are any indexes missing. These DMVs are populated based on queries optimizer has seen so far since last time SQL Server booted up. They are: sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats.

Simly open SQL Server Management Studio of your production server and type:

select * from sys.dm_db_missing_index_details

select * from sys.dm_db_missing_index_groups

select * from sys.dm_db_missing_index_group_stats

and look at the output. You maybe surprised how much entries are there - I suggested one customer to do a quick check and we saw ~400 entries in  sys.dm_db_missing_index_details. This SQL Server environment had multiple databases and was shared between multiple applications, so don't be totally scared with a big number. Still, it shows that there is a big room for improvement to optimize performance of SQL Server installation. I am sure, you will see some in your environment as well. It is especially interesting to see what output is if you just recently migrated your databases from SQL Server 2000 to 2005 or 2008.

Lets have a quick look at the columns of the sys.dm_db_missing_index_details DMV. Our best friend SQL Server books online has this to say about them:

As one can see it is easy to build a CREATE INDEX statement based on the information in this DMV. Unfortunately, you have to do it by hand. I am not yet aware of simple tool which will autoscript all the entries into a SQL script. You may not necessarily want to create all indexes logged in the 'missing indexes DMV'. Try to correlate its entries with frequency of certain queries which can be collected through profiler or looked in the SQL Server plan cache DMVs like sys.dm_exec_query_stats  or other sys.dm_exec_*  DMVs. At the very least, discuss this output to your developers and implement some of them in your Dev environment and see if it helps your performance.

This is all for now. Please collect your juicy fruits! 

Next time I will cover another method of using SQL Server profiler and Database Tuning Advisor.

SQL Server 2008 books online update is available

SQL Server User Group in Sydney CBD

Victor Isakov (MCT, MCITP, MCM, SQL Ranger) is starting a new SQL Server User Group Meeting during lunchtimes in the Sydney CBD. It will run on the 1st Wednesday of every month, from 12:30 to 14:30, at Westpac Place.

This new user group will focus more on best practices, optimization, design and implementation - areas specifically geared towards the corporate sector. The meetings will be of high quality that you would typically see at conferences such as Microsoft Tech Ed, SQL PASS or SQL Connections. A number of industry experts and Microsoft personnel have committed to presenting at this new user group in 2009.

The meetings of this new user group will rotate through 3 core themes:

• Database Administrator (DBA) Focuses on the responsibilities of the Database Administrator, covering areas such as disaster recovery, management, performance tuning, security and operational excellence.
• Database Developer (DBD) Focuses on the responsibilities of the Database Developer, covering areas such as query writing, query tuning, indexing strategies, locking architecture and database design.
• Database Architect (ARC) Focuses on the responsibilities of the Database Architect, covering areas such as high-availability, storage, consolidation / virtualization and scalability.

The details for the new SQL Server User Group are as follows:

It is important to pre-register for the SQL Server User Group Meetings to help facilitate entry into Westpac Place. 

You can come on the day without pre-registering, and are encouraged to "bring a friend" but it will take longer to gain entry.

So please email victor@sqlserversolutions.com.au if you plan to attend.

Otherwise please pass on this information to your colleagues and / or managers so as to encourage attendance. We would like to see the corporate sector encourage attendance and participation in this new SQL Server User Group.

Just tell your manager these meetings represent "free quality training", something that they will surely love in the current economic climate. J

The first three meetings for 2009 are as follows:

Topic: DBA: Best Practices for All DBAs to Follow

Date: 4th February

Description: As the popularity of SQL Server continues to grow, so is the demand for new SQL Server DBAs. Unfortunately, you can't “go to school” to learn how to become a DBA. Most DBAs learn from books, seminars, short classes, and trial and error. This session is designed to encourage a cross-pollination of ideas and best practices between DBAs from different organizations. It includes best practices from the trenches which can only be obtained through experience.

Topic: DBD: A Comprehensive Guide to Indexing in SQL Server

Date: 4th March

Description: With the release of SQL Server 2008 the database developer has more choices in their arsenal for optimally indexing database solutions. In this session we will explore the different indexing technologies that are available in SQL Server 2005 and SQL Server 2008, such as included, composite and filtered indexes. We will discuss the primary design considerations and tradeoffs made when implementing an indexing strategy and present more "exotic" scenarios such as how to implement custom hash indexes, how to combine filtered indexes with sparse columns, partitioned indexes and how to implement partitioned views with different underlying table indexes.

Topic: ARCH: Reducing Your Storage Cost in SQL Server 2008

Date: 1st April

Description: Are increasing storage costs hurting your bottom line as data sizes keep growing throughout your organization? In this session, we explore how SQL Server can help you lower your storage costs. We start with Microsoft SQL Server 2005 partitioning to transparently migrate older data to lower-cost storage tiers over time. We then discuss new storage cost-saving functionality in Microsoft SQL Server 2008 such as data compression, backup compression, and sparse columns, filtered indexes, file stream which can get you to 50% or more reduction in storage requirements for typical applications. Throughout the session, we share experiences, insights, and best practices from SQL Server customers and Microsoft-internal deployments. Learn how to make good use of these technologies in combination.

Free SQL Server 2005 High Availability EBook

Here is a cool perk from Red-Gate: a free EBook by Alan Hirt  ‘Pro SQL Server 2005 High Availability’ http://downloads.red-gate.com/ebooks/DBA_ebook_pack.zip.

Enjoy!

-Yuriy