SQL Server Stuff

Smoke and Mirrors in SQL Server 2008 R2

2010-03-16T21:18:00.000-07:00

Microsoft is touting its new Release 2 of SQL Server 2008. If they weren't planning to charge for it they would be honest and call it a service pack, not a Release. When you consider that SQL Reporting Services arrived in a free service pack for SQL 2000, what R2 offers as a full release is a bit underwhelming.

ENTERPRISE EDITION

R2 has its one flagship feature, self-service business intelligence in the PowerPivot for Excel feature, but that is not available in Standard Edition. I have not used the feature yet but I have my doubts that it will bring business intelligence to the masses as promised.

My opinion, based on my experience, is that the masses don’t want business intelligence. A two dimensional grid like a spreadsheet is about as far as many business users are willing to go.

There is only so much simplification that can be done to allow rank and file users to create and browse multi-dimensional data. (How many business users do you know who can even create and use an Excel pivot table?) Real BI may always be beyond the capacity of the casual business user.

STANDARD EDITION

For the Standard Edition user, there is even less that would drive you to upgrade. The backup compression feature of SQL Server 2008 Enterprise Edition has been added to the Standard Edition of R2. That’s about it.

NEW EDITIONS

R2 introduces two more SQL Server editions, Parallel Data Warehouse and Datacenter. They are dubbed Premium Editions because they come at a distinctly premium price. Either one will set you back $57,498 per processor (list price). However, Microsoft is the only database vendor who counts processor sockets rather than cores in its licensing scheme. So an 8 core processor will cost you the same as a single core. This makes the premium editions an attractive alternative to Oracle where you would need to pay for 8 licenses.

Oddly enough, these two new expensive powerhouse editions may be the most compelling reasons to upgrade, but only for those who need to deal with multi-terabyte databases and huge processing loads. These editions are designed to close the gap with Oracle at the high end of the database spectrum with attractive pricing. If you live on the upper edge of the spectrum you might want to look into what they offer.

A Tool for Herding Cats

2010-01-09T12:48:00.000-08:00

Managing developers is often like herding cats. Many development teams lack formal change control procedures and in many teams everyone has a sysadmin password. Sometimes in smaller companies there is not an adequate test environment and developers acquire the habit of making changes in production. Often they do not communicate those changes to the team lead or the other members of the team.

As we all know, this is a terribly dangerous practice, but we have all done it. Sometimes we have no choice.

if you are responsible for managing a situation like that and the bean-counters are not likely to approve the cost of a real change control platform, here is a script that will make your life a little easier. It will quickly show you the important things that have changed in a database since a point in time that you specify.

declare @cutoffdate datetime
, @days smallint;
set @days = -7; -- change this as necessary
set @cutoffdate = DATEADD(dd, @days,getdate());
select db_name() as DBName
, name
, type_desc
, case when create_date > @cutoffdate then 'New' else 'Changed' end as [Status]
, create_date
, modify_date
from sys.objects
where (modify_date > @cutoffdate or create_date > @cutoffdate)
and is_ms_shipped = 0 -- exclude system objects
and is_published = 0 -- exclude repl tables that will always show change
order by DB_NAME(), type_desc, name;

This is a handy tool for a quick look but it has a lot of limitations. It's redeeming feature is that it is simple and puts no load on the database.

It doesn't show you what change was made or who made it. It doesn't allow you to roll back changes. It doesn't include all the types of database objects (no indexes included for example). Worst of all, it doesn't tell you what has been dropped or deleted.

However, it does alert you to newly created objects and changes for object types that are most relevant to developers: stored procedures, triggers, tables, etc. This will at least allow you to ask around to find out why the change was made and who made it. Most importantly, if the wheels suddenly come off of your website, you can quickly see what, if anything, was changed in the database at the same time the problem started.

Thinking in Sets

2009-12-29T15:43:00.000-08:00

Everyone who programs a database has heard many times that the SQL language is optimized for set based solutions rather than procedural solutions, but examples are seldom provided with that advice. Consequently many beginning SQL programmers don’t have a clear understanding of what ‘set based’ means in terms of the code they need to write to solve a specific problem.

Even for those who have an understanding of the concept, there are many programming problems for which a set based solution seems impossible. Sometimes that is true. It is not always possible to find a set based solution, but most of the time we can find one by using a bit of creative thinking. A good SQL programmer must develop the mental discipline to explore the set based possibilities thoroughly before falling back on the intuitive procedural solution.

In this article we will use a relatively simple example as an illustration of thinking in a set based way about a problem that also has an intuitive procedural solution.

The Business Case

When you visit the doctor’s office, usually the first thing the nurse does is put you on a scale, weigh you and then check your height. Checking your weight makes sense from a medical point of view, but have you ever wondered why the nurse records your height each time? Unless you are very young, your height hasn’t changed since your last visit and is not likely to ever change again.

The reason they always check your height is to guard against identity theft. Healthcare providers want to make sure that the services they provide are going to the person who gets the bill, not to an imposter with a forged identity card.

This kind of identity theft happens more frequently than you might think. HIPAA (Health Insurance Portability and Accountability Act) regulations now require an audit of changes in permanent physical characteristics in a patient’s history that might suggest identity theft.

Querying this kind of information provides us a good example for comparing procedural thinking and set based thinking when programming in SQL.

The Problem Statement

The generic programming problem is that the solution depends on the order of rows and requires the comparison of current row values with values in previous rows. This is a type of problem in which the procedural solution is intuitive but the set based solution is not so obvious.

In this particular problem we are looking for rows where the previous record for the same patient has a value for height that is different than the height on the current record. We want to return the patient’s unique medical record number, the date the change occurred, what the height was changed from and what the height was changed to. We do not want to return any records that do not mark a change in height for a patient.

A Procedural Approach

The intuitive, procedural way to attack this problem is to loop through the records for each patient one row at a time. We query the first record for the patient and save the patient’s original height in a variable. Then we loop through subsequent records for the patient, comparing height values. If we find the height is different on a subsequent record, we write an audit record, update the height variable with the current value and continue looping through the rows. Then we move to the next patient, perhaps using an outer cursor to provide the patient id to the inner cursor.

Here is code that would implement a procedural, looping method:

create table #HeightAudit -- output table(MedRecNumber varchar(20), CreateDate datetime, Height varchar(10), CurrentHeight varchar(10))

–- get all the patients

declare MRN_cur Cursor for select distinct MedRecNumber
from PatientInfo.dbo.PatientInfo
Open MRN_cur;Fetch next from MRN_Cur into @MedRecNumber

While @@FETCH_STATUS = 0
Begin -- outer loop
-- do this for every visit for the medical record number

declare Visit_cur Cursor for
select CreateDate , Height
from PatientInfo
where MedRecNumber = @MedRecNumber
order by CreateDate

open Visit_cur;

fetch next from Visit_cur into @CreateDate, @Height;

–- establish the starting height for each patient

Set @CurrentHeight = @Height

While @@FETCH_STATUS = 0
Begin -- inner loop

If @Height <> @CurrentHeight
begin

insert #HeightAudit values (@MedRecNumber, @CreateDate, @Height, @CurrentHeight)

Set @CurrentHeight = @Height end

fetch next from Visit_cur into @CreateDate, @Height;

end -- inner loop

close Visit_cur;
deallocate Visit_cur;

Fetch next from MRN_Cur into @MedRecNumber

End -- outer loop

close MRN_Cur;
deallocate MRN_Cur

Select * from #HeightAudit
drop table #HeightAudit

This works, but it is an inefficient method. It could be a serious performance problem when working with a large number of rows. How can we do this in a set based and presumably more efficient way?

A Set Based Approach

The difference between a procedural and set based solution comes down to the way you define the problem. Stated in its simplest form, the change we are interested in involves only two records, two consecutive visits by the same patient. Everything else is irrelevant.

We might start by ordering the data by the patient’s unique Medical Record Number (MRN) and then the visit date. In that way, the records of consequtive visits by the same patient are adjacent to each other. The problem is then reduced to finding a way to join consecutive records from this set.

When we understand the problem in that way the solution is not so difficult to discover. We need to create a sequence number for the sorted rows that can be used to join one record with the next in a self-join.

We can create a temporary table with the patient data and a column populated with an identity function to create a sequence id that will correspond to the order of rows in the table.
After a little thought we come up with a self-join condition like this to join each row with it’s adjacent row:

... from #tmptable t1
join #tmptable t2 on t1.SequenceNumber = (t2.SequenceNumber + 1)

The resulting set of records represents every possible opportunity for the value of the patient’s height to change i.e. a set of records such that each contains the data from each set of 2 consequtive records. At this point filtering out the records that do not represent a change is trivial. We simply review our statement of the problem: To qualify as a record of interest, the patient must be the same in consecutive visits and the two heights must be different. The code might look like this:

-- Code that creates the temporary table

select IDENTITY(bigint, 1, 1) AS SequenceNumber, PatientID, VisitDate as ChangeDate, Heightinto #OrderedInfo
into #OrderedInfo
from TblPatientAudit
where VisitDate between @StartDate
and @EndDate
order by PatientID, VisitDate;

-- query that returns a record for each change in height

select ord.PatientID
, ord.ChangeDate
, ord.Height as 'HeightChangedTo'
,ord2.Height as 'HeightChangedFrom'
from #OrderedInfo ord
join #OrderedInfo ord2 on ord.SequenceNumber = (ord2.SequenceNumber + 1)
Where (ord.Height<> ord2.Height –- this is a change in height
and ord.PatientID = ord2.PatientID) –- for the same patientorder by ord.PatientID, ord.ChangeDate, ord.SequenceNumber;

Relative performance of the two methods

For testing, we ran both the cursor and the set based code against a table containing 21,000 patient visits which contained 3200 changes of a patient’s height. The cursor based code ran in 4 minutes, 7 seconds. The set based code ran in less than one second. Both returned the same results.

The auditing requirements for a large healthcare provider can easily generate a million rows per day in the audit table. So, even if you run your audit reports for only a single day’s data, you will have a lot of rows to process, far too many for a cursor or other looping mechanism to handle efficiently.

Set Based Thinking

Note that both steps of this solution operate on sets of data, not on individual rows. compare this to the cursor solution where most operations are repeated for each row in a set. However, keep in mind as you search for set based solutions that a set can contain 1 or even zero rows. Operating on a set containing only one row however is not the same as iterating through the rows of a set. There is a subtle but important difference.

Nothing in this simple example is rocket science. You will encounter SQL problems that are much more difficult to solve in a set based way and some that are impossible. However even this example requires a significant mental adjustment for programmers who are new to SQL programming. It requires a conscious effort to pull yourself out of your comfort zone and think in an unaccustomed manner. Even in the most difficult situations, don’t give up on a set based solution until you have given it a fair amount of thought.

Back in the Saddle Again

2009-12-29T15:08:00.000-08:00

I haven't touched this blog for several months. Most of my available writing time has been absorbed by the launch of our free monthly SQL Server newsletter and by an article I wrote for SQL Server magazine (to be published Feb 2010). However, it's resolution time and my resolution this year is to make posts on a more regular basis.

if you would like to get the free monthly SQL newsletter emailed to you, follow the link in the left panel of this blog and sign up. Your email address will only be used to send you the newsletter once per month. We won't spam you and we will not give your email address to anyone else.

TSQL change in SQL 2008 can break legacy code

2009-07-11T11:15:00.000-07:00

As of the 2008 version, SQL Server no longer supports the old antiquatedd syntax for outer joins. It now requires the use of the newer JOIN keyword syntax. While it is a good idea to get rid of this flawed syntax, there is a considerable amount of legacy code that uses it. Before upgrading to SQL 2008 you should search your TSQL code for examples of the old outer join operators ( *=, =*) to see if this is going to affect your application.

Here is an example of the old syntax using the AdventureWorks sample database:

SELECT SalesTerritory.Name, SalesPerson.SalesPersonID
FROM Sales.SalesTerritory, SALES.SalesPerson
WHERE Sales.SalesPerson.TerritoryID *= Sales.SalesTerritory.TerritoryID

This syntax will generate an error in SQL Server 2008. To get around this you need to change the code to the accepted ANSI syntax or else set the database into compatibility level 80. Here is an example of the supported syntax

SELECT SalesTerritory.Name, SalesPerson.SalesPersonID
FROM Sales.SalesTerritory
LEFT JOIN Sales.SalesPerson on Sales.SalesPerson.TerritoryID = Sales.SalesTerritory.TerritoryID

So far Microsoft has only de-supported the outer join form of this syntax. The inner join form will still work. However, it should not be used in new code. I suspect it will be de-supported in a future release as it should be. Specifying join conditions in the WHERE clause makes it far too easy to leave a join condition out of your WHERE clause, especially in queries that join many tables.

The Perils of XML Over-Use

2009-05-02T12:09:00.000-07:00

Like all technologies, XML has its strong and weak points. It is useful in many situations. However it is often used in places where it is not appropriate. The performance cost of XML mis-use or over-use can be enormous.

I recently looked at the results of a performance comparison between data stored in 2 relational tables and the same data stored in a single xml-format table. The data consisted of approximately 100,000 orders and 1.2 million order line items. In the relational model they were stored as an Orders table and an OrderDetail table. The xml data was created in element format in one case and in attribute format in another. Both relational and xml data had similar indexes.

Below is a table summarizing the results. A cold execution means the query was executed after the buffers were flushed and no data was in cache. A hot execution was done after the data was in cache:

Data Format	Cold Execution (ms}	Hot Execution (ms)	Storage (mb)
XML Element	59373	37054	397
XML Attribute	3190	31	407
Relational Tables	250	1	214

As you can see there is a HUGE performance degradation using element-style XML. It is on the order of 37,000% slower when hot execution times are compared with relational data retrieval. Attribute style XML fares better but it is still many times less efficient than data retrieval from relational tables.

As well as having performance issues, both forms of xml take almost twice the disk space of relational data as can be seen the Storage column of the table above.

XML has many legitimate uses in a database environment. It is a universally recognized data interchange format. It is self-documenting. It is ideal for exchanging small packets of information, storing configuration settings, etc. But high performance manipulation of large datasets is not something it does very efficiently.

Here is a URL to the actual test parameters and detailed results:

http://www.sqlsolutions.com/articles/articles/Performance%20of%20indexing%20of%20XML%20in%20MS%20SQL%202005.html

Get table sizes painlessly

2009-03-25T15:52:00.000-07:00

Here is a handy script to get a quick look at table sizes for all tables in your database without putting unneeded load on a production server. It has no detrimental effect on performance. It gets row and page counts from the sysindexes table without having to touch your user tables. These counts are not exact but they are accurate enough for most purposes. The data is updated each time your statistics are updated and is usually within a fraction of a percent of the current values.

I use this query for a number of purposes. When I take a first look at a database, it gives me an idea of the size of the major tables. When performance tuning, I sort it by table name so I can quickly find the relative sizes of the tables in the query I am trying to optimize. It is easy to locate tables that were created for some purpose but never used. You can also use it to record database growth by taking snapshots at regular intervals.

I did not write this code as a stored procedure because in my work as a consultant, often I cannot create objects on the client’s production SQL Server. Scripts are handier and easier to modify for particular situations. You, however are free to enclose it in a stored procedure if that is better for you.

There is nothing clever about the code, but it is useful. The query returns the table name, the number of rows it contains, and the number of 8kb pages it contains, including pages used to store binary large objects like ntext and image data. It will run on all versions of SQL Server.

If Microsoft ever makes good on its threat to eliminate direct access to the system tables, then we will have to create a version that selects from the sys.objects and sys.indexes views. But right now it is simpler to have a single query that works on all versions.

-- GetTableSizes.sql

select o.name
, sum(i.rows) as rows
, sum(case when indid < 2 then i.dpages
when indid=255 then i.used
else 0 end) as allpages
from sysobjects o
join sysindexes i on o.id = i.id
where (indid < 2 or indid = 255)
and o.name not like 'sys%'
group by o.name, indid
order by rows desc

My head in the Cloud

2009-02-10T11:56:00.000-08:00

Microsoft’s announced future direction is for SQL Server to become a “data platform” encompassing not only traditionally structured database data, but unstructured information such as text documents, xml documents, spreadsheets, streaming media, etc. Furthermore, they foresee that sooner rather than later, data management and storage will be a web service enshrouded in the Cloud, which has become a techno-chic synonym for the internet. The idea is that you don’t need to know where the data is stored or where your database management system is located. You just request data from a url somewhere (probably China where the technician work very cheap). When you need it, it appears out of the cloud.

I have my doubts that this will find quick acceptance. The internet is by nature unreliable and insecure. To date, the hackers are staying comfortably ahead of the hacker police.

I could be wrong and often am. No doubt such a scheme could be a great deal less expensive than maintaining your own database infrastructure. I am curious to know how many of you would delegate the safety and accessibility of your mission-critical data to the Cloud? If you have a minute, could you email me your thoughts on this?

ksurvance@sql-consulting.com
______________________

TOP and ORDER BY operators in SQL 2005/8

2009-01-11T20:51:00.000-08:00

Changes to the TSQL language beginning with SQL Server 2005 can change the order of results returned from a query selecting from a view. That in turn, has the potential to break some application functionality. Most sites will be unaffected, but some may find that certain code is broken. If your application depends on sorting results from a view defined as:

SELECT TOP 100 PERCENT…
…
…ORDER BY THISCOLUMN

You might be affected by this change.

Simply put, a view created with an ORDER BY clause is no longer guaranteed to return rows in the specified order. In order to guarantee the order of the result set In versions newer than SQL Server 2000 you must sort the result set in the query that selects from the view, not in the view definition.

If you are planning an upgrade from SQL 2000, you certainly want to be aware of this so you can test your existing code. If you have already upgraded you may want to make sure that subtle changes in application functionality have not gone unnoticed.

Views that are created with a TOP operator and an ORDER BY clause are still guaranteed to return the same rows, but the rows are not guaranteed to by in the sequence defined by the ORDER BY clause. In most cases the order will be correct, but not in all cases.

This is not a bug, it is a deliberate change made for the purpose of bringing TSQL into closer compliance with the ANSI SQL language standard. Views were never intended to return sorted results. But, because it was possible in previous versions of SQL Server, there is a significant amount of code in productions systems that will be affected by this change. Microsoft has a hotfix for later SQL versions which restores default SQL 2000 behavior. They describe it as a temporary fix designed to give you time to rewrite any code that is broken, so it is not likely to be around for very long.

If you are interested in the technical details of why this was made, I recommend looking at this article by Itzak Ben Gann in SQL Server magazine, January 2009:

http://www.sqlmag.com/Articles/ArticleID/100697/100697.html

SQL 2008 cluster installer bug

2008-12-23T10:26:00.000-08:00

I just got back from an on-site assignment in Denver. W were migrating to SQL 2008 on a new set of Windows 2008 clustered servers and ran into an installer bug. We completed the install of the first node but the install failed for the second node because of an invalid product SKU. The SKU was valid. It was slipstreamed in by Microsoft when the product was purchased and downloaded. It appeared correctly in the SKU input box just before the error. We tried several times with the same result and eventually called Microsoft.

Microsoft is well aware of the bug and has a hotfix which, unfortunately, didn't work for us either. Microsoft and others have also posted workarounds which all involve running setup from the command prompt with a varying number of parameters, depending on who you are talking to. Microsoft's example has an enormous number of parameters which you can find by following links from these pages: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=363036

http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/13a256b0-b95b-46a7-92d5-17a0b763f8dd/

What finally worked for us was very simple: Execute this in the command prompt while logged in as an admin:

setup.exe /ACTION=AddNode /INSTANCENAME="MSSQLSERVER"

By the way, the install process for clustered instances has changed significantly. Now the cluster wizard has been broken into two wizards, one creates a single-node cluster (whatever that means). Then you must go back to the setup start screen and run the Add Node wizard for each node you want to add. You must run it from the node you want to add, not the primary node. SQL Server binaries are not moved to the second node until you do that.

Amateurism and SQL Server

2008-12-05T18:33:00.000-08:00

The Marketing department drives everything at Microsoft, especially product design and development. A while back this group decided that SQL Server was an "administration-free" database. Their premise is that my grandmother should be able to design, construct and manage an enterprise data platform without having to bother about learning much of anything.

For example, the table creation wizard allows a completely unqualified person to actually create a table. It defaults to creating a primary key on an identity column using a clustered index.
This does insure that the table 1) has a PK 2) has a clustered index, and 3) the clustered index isn't going to cause page splitting, even though the table creator has little idea what any of this means. This is a good thing for someone with a small startup that needs a database and can't afford professional help. However, I see it in large corporations done by people who are considered professionals.

I have had discussions with many of them and found that because the wizard's default behavior, They believed that a primary key HAD to be clustered. Also because of the wizard's defaults, they have confused an identity with a real primary key. When I ask them what other column or group of columns is unique in the table, they don't know what I'm asking.

While helpful for an amatuer application, these defaults virtually assure that the database will not scale as a company grows. The placement of the clustered index is critical to good performance. Placing it on an identity in every case is a big mistake.

A nonclustered index will handle an identity lookup as well as a clustered index, so why waste the only clustered index on something that can be done as well by a nonclustered index? Clustered indexes can do certain things much better than nonclustered indexes, like range searches returning many records.

It only makes sense to place the clustered index on a column where it will make a difference. However, deciding which column to place it on requires knowledge and creative thought of which the wizard is not capable and apparently many db designers are not either. It is not that they don't know how to handle the issue: it is that they don't even know there is an issue.

Next post, why an identity is not a primary key.

Vista rant

2008-11-25T12:17:00.000-08:00

If finally bit the bullet and bought a new laptop. I have avoided doing this for years because of the tremendous time investment necessary to load software and configure the system to do the things you need to do. Since I only do this every few years, I generally buy on the cutting edge of technology. It's pretty clear that x64 is the future of PCs so I opted for Vista Business x64 on a loaded Lenovo T61 (4gb, fast drive, etc). I finally have it under control, but it has been a struggle.

There are a lot of 32 bit programs that won't run on Vista x64 and the x64 versions don't exist yet. For example, we use Quickbooks for accounting and I use the Quickbooks timer to record billable time when I am working. I used to use it, apparently. It won't run on Vista at all.

You run into lots of issues like this, especially if you upgrade rather than do a clean install. I am fortunate that mine was a pre-install on the new laptop because I have found in my research that many programs I need and that are supposed to be compatible will only run on a clean install.

If you are thinking about Vista x64, you have to make sure the software you need is available on x64 before you buy.

As a prelude to my rant on Vista itself I want to mention that I am not a knee-jerk Microsoft basher. I make my living supporting a Microsoft product. But I am not an apologist either.

Microsoft has not made Vista an intrinsically more secure operation system. They have merely shifted the responsibility for doing unsafe operations from the OS to the user. Vista was probably designed by their legal department. Every time you start a non-MS program or change any sort of configuration option, you get a pop-up warning; Likewise when you open a website.

Now, I am a geek. I have been using computers since the days of 64k CPM programming. When I can't understand what the message means, how is a casual user supposed to know what to do with the pop-up?

Vista locks your computer down to an unusable degree. If you take all their defaults, you won't be able to do much with your computer.

The item that took the most time to resolve was the change in Remote Desktop. There are a lot of people on the web complaining about it, but I didn't find the answer there. It took trial and error to discover that in many circumstances you must use a full qualified domain name or ip address for the target machine on remote networks over a vpn connection. Just the hostname doesn't work. the vpn opens just fine, but then you cannot see or connect to any machine on the remote network.

Networking in general is a problem. The GUI interfaces and terminology have changed substantially.

Worst of all is the abstraction they have laid over the file system. It requires some effort to see the actual folder hierarchy. I know there are a lot of users out there who don't understand a hierarchical file system. They save documents and can't find them. However, I disapprove of dumbing down the user interface to make it more difficult for an expert user to do what they need to do.

Denormalization

2008-10-30T16:35:00.000-07:00

I'm convinced that denormalization is a word used mostly by people who don't know how to normalize a schema in the first place. They generally fall into the pose of being a "real-world' dba as opposed to a ridiculous academic or relational purist. Often, the real-world dba's say they have denormalized a database when they have merely failed to normalize it.

Of course, denormalization is something that is often useful and occasionally necessary. However, my belief is that you should first put the schema into 3rd normal form and only then consider denormalization. Until you find a normal form for the schema how can you know whether you have properly understood the process you are trying to model?

Often denormalizers do not understand the long-term implications of what they are doing and build serious long-term performance problems into their design. Typically, you will find that a lot of cursors, colossal union queries, table-valued functions, etc are necessary to patch together the data in a poorly normalized design.

After a database has been in service for months or years it is almost impossible to make substantial schema changes to correct the problems. Often man-years of development have gone into the applications accessing the database and making all the changes that would be necessary is just not an affordable course of action. Developers are reduced to putting band-aids on band-aids to keep their applications chugging along.

More DMV's - Code Performance

2008-10-13T19:37:00.000-07:00

I borrowed the original code from a Microsoft engineer who had posted it on the Microsoft website. I would credit him but I forgot who he was and I can't find the post now. At any rate I have added my touches.

This query helps you identify the code that is giving you the biggest problems. It pulls out execution information for every plan in the procedure cache. It aggregates totals and averages for key performance metrics, logical i/o, cpu, etc. It comes with multiple ORDER BY clauses so you can highlight the biggest users of any particular resource. Uncomment the clause you want to use.

The one I use most is logical i/o. I think that is the truest measure of code efficiency. Execution time will vary depending on a lot of factors not directly related to the efficiency of the code and indexing. If the data is in cache, the query will run a lot faster than if it has to page data off the disk. Blocking or a heavy load on the server will also change the execution time of a statement.

Physical i/o is an accident involving how much of the data happens to be in cache when the statement runs. It fluctuates randomly and is not a reliable metric.

But, given the same code, same data, same indexing and the same query plan, logical page reads will always be the same. That gives you a baseline to start from when you are optimizing. You can try indexing tricks, code modification, or forcing different query plans. If logical i/o is reduced, you know you are making progress.

Here is the code. I have throttled it down to the top 100 because a big system can have a huge number of plans in cache and you are only interested in the heavy-hitters anyway. I'll bet you already know that the procdure cache is flushed at each restart of sql server or execution of 'DBCC FREEPROCCACHE' so I won't mention it.

SELECT TOP 100 t.[text] AS [Batch],
SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
((CASE qs.[statement_end_offset]
WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
- qs.[statement_start_offset])/2) + 1) AS [Statement]
, qs.[execution_count] AS [Count]
, qs.[total_worker_time] AS [Tot_CPU], (qs.[total_worker_time] /
qs.[execution_count]) AS [Avg_CPU]
, qs.[total_physical_reads] AS [Tot_Phys_Reads],
(qs.[total_physical_reads] / qs.[execution_count]) AS [Avg_Phys_Reads]
, qs.[total_logical_writes] AS [Tot_Logic_Writes],
(qs.[total_logical_writes] / qs.[execution_count]) AS [Avg_Logic_Writes]
, qs.[total_logical_reads] AS [Tot_Logic_Reads],
(qs.[total_logical_reads] / qs.[execution_count]) AS [Avg_Logic_Reads]
, qs.[total_clr_time] AS [Tot_CLR_Time], (qs.[total_clr_time] /
qs.[execution_count]) AS [Avg_CLR_Time]
, qs.[total_elapsed_time] AS [Tot_Elapsed_Time], (qs.[total_elapsed_time]
/ qs.[execution_count]) AS [Avg_Elapsed_Time]
, qs.[last_execution_time] AS [Last_Exec], qs.[creation_time] AS [Creation Time]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
-- ORDER BY [Tot_CPU] DESC
-- ORDER BY [Tot_Phys_Reads] DESC
-- ORDER BY [Tot_Logic_Writes] DESC
ORDER BY [Tot_Logic_Reads] DESC
-- ORDER BY [Avg_Logic_Reads] DESC
-- ORDER BY [Tot_CLR_Time] DESC
-- ORDER BY [Tot_Elapsed_Time] DESC
-- ORDER BY [Count] DESC

Using Data Management Views - Blocking

2008-10-09T20:34:00.000-07:00

Finally, here's something useful:

Data management views and functions in SQL 2005 give you an easy way to look at critical server information but there is not a lot of documentation about using them. Here is an example of using a couple to record blocking information, including the blocker code and the blocked code. I've written it to run in an endless loop taking snapshots of blocking on the server at 5 second intervals. The interval is configurable of course. If you have ever tried digging this information out of a server by using sp_blocker_pss80, you will love the simplicity of this method.

In this example I have included code to create a temp table to store results but you might want to create a permanent table somewhere.

The loop will run from a query window until you kill it, then it will retrieve results and display them. This has the disadvantage of not letting you see results until you are finished. If you want to view blocking interactively you can just execute the SELECT part of the INSERT statement in another window.

Heres the code to create the table:

/*
create table #BlockingCode
(resource_type nvarchar(120)
,[database] nvarchar(128)
,[blk object] bigint
,request_mode nvarchar(120)
,request_session_id int
,waiter_text text
,blocking_session_id int
,blocker_text text
,batchdate datetime default getdate() )
*/

-- here's the code to capture and insert blocking information.

WHILE 1=1 -- endless loop, be careful
BEGIN
insert into #BlockingCode
(resource_type
,[database]
,[blk object]
,request_mode
,request_session_id
,waiter_text
,blocking_session_id
,blocker_text
)
select t1.resource_type
,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk object]
,t1.request_mode
,t1.request_session_id -- spid of waiter
,(select text from sys.dm_exec_requests as r --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle) where r.session_id = t1.request_session_id) as waiter_text
,t2.blocking_session_id -- spid of blocker
,(select text from sys.sysprocesses as p --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as blocker_text
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address
WAITFOR DELAY '00:00:05'
END
GO

--retrieve results
select * from #BlockingCode

This code has the disadvantage of not showing the results until you stop loop execution, then it displays the results collected. If you want to take a quick look at what kind of blocking is going on, just copy the select clause of the insert statement into another query window and execute it when you wish.

Object\Relational Mapping and performance

2008-10-08T12:33:00.001-07:00

One hears a lot about the impedence mismatch between modern object-oriented programming languages used for application development and 40 year-old SQL that is still used for the heavy-lifting in the database. Object/Relational mapping tools were developed to fix the mismatch. One day they will discover that they are trying to fix the mismatch from the wrong end. Until then, we will have to deal with the sometimes huge performance hit imposed by these tools.

Procedural programers love object/relational tools because they can write data manipulation code in the same object-oriented manner as their other application code. They love them so much that they often overlook the performance implications until it is too late and, even after, they have a hard time giving them up. Here's an illustration:

A while back, I got a call from a client in Silicon Valley. Their web application had suddenly had a dramatic downturn in performance. I jumped on a plane. In a very short time I found that there was one statement that was reading about 40 million 8k data pages every time it executed. It took about 12 minutes to run and was paralyzing the server. I took the SQL it was executing and ran it through query analyzer. It read about 10,000 pages and ran in a couple of seconds.

I went to the head of development and the developer himself with this information so they could straighten out the code. It had been written using an O/R mapping tool by an evangelistic java programmer without anyone else's knowledge. It was the only piece of code in a huge application that used o/r mapping and it had brought the application down.

There's more:

When I am on that client site they put me in the developers email group so I can follow all the conversations. By chance, later on the same day one developer sent an email around mentioning that he had read some things about this cool new O/R tool. The developer who had single-handedly brought down the application with this tool jumped into the conversation announcing that he was already using it and it worked great. He did not say a thing about performance.

Starting Here

2008-10-07T20:55:00.000-07:00

Who am I and why do I think my thoughts about SQL Server would be useful enough for anyone to want to read them?

I am Kurt Survance, chief consultant and owner of SQL Consulting, Inc. http://sqlconsulting.com. My Microsoft certification card is dated 1993. It carries certifications for MCSE, MCDBA and MCP and is number 2535 in a series that now stretches past one million. You can see that I have been in this game longer than most. However, I would not recommend getting old just so you can be experienced.

I just got back from Redmond where I served on a panel of Subject Matter Experts (SME) reviewing one of the SQL 2008 exams. I am on the other end of the certification game now.

So much for experience. It is important but it's not everything. Eventually I will get around to writing something useful but for a little while I will be busy redesigning the look and feel of the blog page.