SQL Server Query Performance Analysis

Great post by Carl Nolan(http://blogs.msdn.com/b/mcsuksoldev/archive/2011/11/27/adventure-in-tsql-sql-server-query-performance-analysis-using-dmvs.aspx) on finding the worst offending queries that do the most CPU and Disk I/O loads.

The two queries are:

CPU Query

-- Which Queries are taking the most time/cpu to execute
SELECT TOP 20
    total_worker_time, total_elapsed_time,
    total_worker_time/execution_count AS avg_cpu_cost, execution_count,
    (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '')
        FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
    (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset) / 2
        )
        FROM sys.dm_exec_sql_text([sql_handle]) AS est) AS query_text,
    total_logical_reads/execution_count AS avg_logical_reads,
    total_logical_writes/execution_count AS avg_logical_writes,
    last_worker_time, min_worker_time, max_worker_time,
    last_elapsed_time, min_elapsed_time, max_elapsed_time,
    plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
    OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND DB_NAME(dbid) IS NOT NULL
  AND (total_worker_time/execution_count) > 100
--ORDER BY avg_cpu_cost DESC;
--ORDER BY execution_count DESC;
ORDER BY total_worker_time DESC;

 

Disk IO Query

SELECT TOP 20
    total_logical_reads/execution_count AS avg_logical_reads,
    total_logical_writes/execution_count AS avg_logical_writes,
    total_worker_time/execution_count AS avg_cpu_cost, execution_count,
    total_worker_time, total_logical_reads, total_logical_writes,
    (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '')
        FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
    (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset
        ) / 2)
        FROM sys.dm_exec_sql_text(sql_handle) AS est) AS query_text,
    last_logical_reads, min_logical_reads, max_logical_reads,
    last_logical_writes, min_logical_writes, max_logical_writes,
    total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads,
    (total_logical_reads + (total_logical_writes * 5))/execution_count AS io_weighting,
    plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
    OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND DB_NAME(dbid) IS NOT NULL
  and (total_worker_time/execution_count) > 100
ORDER BY io_weighting DESC;
--ORDER BY avg_logical_reads DESC;
--ORDER BY avg_logical_writes DESC;
--ORDER BY avg_cpu_cost DESC;

Dictionary Not Found

Kept getting the error: "Dictionary Not Found..." once I uploaded new changes to the web server to allow spell checking. So, my first step was to open the developer tools in IE (F12) and do a quick capture of the network traffic.

Which gave:

URL: /fieldperformance/C1Spell_en-US.dct
Method: GET
Result: 404
Type: text/html
Received: 1.37 KB
Taken: 265 ms 
Initiator:
Wait‎‎: 1482
Start: 62
Request: 203
Response‎: 0
Cache: 0
read‎‎ Gap‎‎: 5067

So, it's looking in the fieldperformance folder for the dictionary file. Doing a quick check, yes the file is located there. The problem then ends up being the MIME type is missing.

Going to the IIS Manager, I added the Extension dct with a MIME type of application/octet-stream.

WCF & Silverlight max buffer size issue

Ran into these problems a few times with the WCF errors, in which the buffer size was the issue:

• “The remote server returned an error: NotFound”
• "Unable to read data from the transport connection: The connection was closed"
• "The maximum message size quota for incoming messages (65536) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element."

This would be an easy solution if only dealing with the WCF. Since, we are using the WCF layer to pass data to/from the Silverlight layer the problem arises in two locations -- not one.

Over at Mehroz's Experiments (http://smehrozalam.wordpress.com/2009/01/29/retrieving-huge-amount-of-data-from-wcf-service-in-silverlight-application/) he goes into good detail on solving this problem.

So in the Silverlight's ServiceReferences.ClientConfig we increase the buffer size.

Code Snippet

 <binding name="BasicHttpBinding_IScientificDataService"   closeTimeout="00:01:00"openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00" maxBufferSize="2147483647" maxReceivedMessageSize="2147483647" textEncoding="utf-8" transferMode="Buffered">
    <security mode="None"/>
binding>

In the WCF's Web.config, we needed to increase the maxBufferSize and maxReceivedMessageSize to a larger number Code Snippet   <system.serviceModel>     <behaviors>       <serviceBehaviors>         <behavior>           <serviceMetadata httpGetEnabled="true" />           <serviceDebug includeExceptionDetailInFaults="true" />           <dataContractSerializer maxItemsInObjectGraph="2147483647"/>         behavior>       serviceBehaviors>     behaviors>     <serviceHostingEnvironment multipleSiteBindingsEnabled="true" aspNetCompatibilityEnabled="false" />   system.serviceModel>

SQL String Manipulations

Interesting article in SQL Server Magazine about String Manipulations by Itzik Ben-Gan (http://www.sqlmag.com/article/tsql/string-manipulation-tips-techniques-part-1-136427)

So to give me a quick reference in the future, I'll summarize it here:

Counting Occurrences of a subString within a string:

DECLARE
  @str    AS VARCHAR(1000) = 'abchellodehellofhello',
  @substr AS VARCHAR(1000) = 'hello';

SELECT (LEN(@str) - LEN(REPLACE(@str, @substr, ''))) / LEN(@substr);


Exactly N Occurrences of a substring within a string:

DECLARE
  @str    AS VARCHAR(1000) = 'abchellodehellofhello',
  @substr AS VARCHAR(1000) = 'hello',
  @N      AS INT           = 3;

SELECT
  CASE
    WHEN (LEN(@str) - LEN(REPLACE(@str, @substr, ''))) / LEN(@substr) = @N
      THEN 'True'
    ELSE 'False or Unknown'
  END;
-OR-

SELECT
  CASE
    WHEN @str LIKE '%' + REPLICATE(@substr + '%', @N)
         AND @str NOT LIKE '%' + REPLICATE(@substr + '%', @N+1)
      THEN 'True'
    ELSE 'False or Unknown'
  END;

Replacing Multiple Contiguous Spaces with a single space:

DECLARE @str AS VARCHAR(1000) = 'this   is     a   string    with     lots   of   spaces';

SELECT REPLACE(REPLACE(REPLACE(@str, ' ', '~ '), ' ~', ''), '~ ', ' ');

Replacing Overlapping Occurrences:

DECLARE @str AS VARCHAR(1000) = '.x.x.x.x.';

SELECT REPLACE(REPLACE(@str, '.x.', '.y.'), '.x.', '.y.');

-OR-

SELECT REPLACE(REPLACE(REPLACE(@str, '.', '..'), '.x.', '.y.'), '..', '.');
String Formatting Numbers with Leading Zeros:

DECLARE @num AS INT = -1759;

SELECT CASE SIGN(@num) WHEN -1 THEN '-' ELSE '' END + REPLACE(STR(ABS(@num), 10), ' ', '0');

-OR-

SELECT CASE SIGN(@num) WHEN -1 THEN '-' ELSE '' END + RIGHT('000000000' + CAST(ABS(@num) AS VARCHAR(10)), 10);

-OR (In Denali)-

SELECT FORMAT(@num, '0000000000');

Left Trimming Leading Occurrences of a Character:

DECLARE @str AS VARCHAR(100) = '0000001709';

SELECT REPLACE(LTRIM(REPLACE(@str, '0', ' ')), ' ', '0');

Checking That a String Is Made of Only Digits:

DECLARE @str AS VARCHAR(1000) = '1759';
SELECT
  CASE
    WHEN @str NOT LIKE '%[^0-9]%' THEN 'True'
    ELSE 'False or Unknown'
  END;

-OR-

CHECK (col1 NOT LIKE '%[^0-9]%')

Median in SQL

Median is not a default aggregate in SQL-Server, but is sometime a perferable statistical function than Average. So here is quick tip on how I got the median:

Define: Median -

"The median of a finite list of numbers can be found by arranging all the observations from lowest value to highest value and picking the middle one. If there is an even number of observations, then there is no single middle value; the median is then usually defined to be the mean of the two middle values" - Wikipedia http://en.wikipedia.org/wiki/Median

By using ROW_NUMBER() function twice I can get my approximation of where the median is located.

For Example:
ROW_NUMBER() OVER (PARTITION BY Date, Array, Inverter ORDER BY Current ASC

-and-

ROW_NUMBER() OVER (PARTITION BY Date, Array, Inverter ORDER BY Current DESC

By using these two values I can pull the absolute value from their subtraction

ABS(ASCRow - DescRow)

To get an approximation of where it is associated with the median. Now if the values you are sorting are distinct, then you can find the median by looking for numbers that are less than or equal to 1 and then taking their average.

ABS(ASCRow - DescRow) <=1

In my case I can get more than a count of 2 numbers that are at the median, in this case I did another ROW_NUMBER function but this time order on the absolute difference and then I selected the top row in an outer query:

ROW_NUMBER() OVER (PARTITION BY Date, Array, Inverter ORDER BY MedianDistance) as ROW_NUM


This is where I stopped, even though there is still the chance of not getting the "true" Median. I would basically need to go further and pull all data points with the same value as the MedianDistance for those partitions and do an average.

One cause of Silverlight's White Screen of Death (WSOD)

We have this silverlight (4.0) website that would work for most, but a select few were only seeing a white screen. I tried running the website in different IE formats and even in firefox. The results were the same no matter what browsers were used. So we narrowed it down to something with the machine.

A white screen usually means that there is a silverlight error, which can be tough to trace/track down. In our case it came down to the issue that nested user controls cannot have static resources. Don't know how this applies to the machine level is beyond my comprehension.

So instead of this:
 <usercontrol.resources>
  <style targettype="sdk:DataGridColumnHeader" x:key="DataGridHeader">
      <setter Property="FontSize" Value="10"/>
      <setter Property="Padding" Value="3"/>
      <setter Property="Margin" Value="0"/>
 </style>
</usercontrol.resources>

We needed to put the style more inline:
<sdk:datagrid.columnheaderstyle>
   <style targettype="sdk:DataGridColumnHeader">
      <setter Property="FontSize" Value="10"/>
      <setter Property="Padding" Value="3"/>
      <setter Property="Margin" Value="0"/>
 </style>
</sdk:datagrid.columnheaderstyle>

Another SQL Trick/Tip

I had a request to change a view to display information as a result of "Zones" instead of "Observation". So, a little background...

For each processed file, after inserting, I set an observation number based on the row in the file. This is easily done with the ROW_NUMBER() function in SQL-Server:

ROW_NUMBER() OVER (ORDER BY ObservationSQN) AS ROWNUM

,and some other unique tricks I use to separate it out per file.

The request then asked, instead of displaying Observation Numbers, they wanted an average; and if the number is less than or equal to 0 then don't include that observation in the average.

Zone 1 has the average of observations 1,2,3,4,6
Zone 2 has the average of observations 5,8,11
Zone 3 has the average of observations 10,12,13,14,15

Well, thankfully SQL-Server doesn't include a value in it's average function if it's null. Just have to set the value to NULL if <= 0

So at first I did this brute force technique (which ends up being ugly and slow):
SELECT
avg(zone1.value) as zone1Avg,
avg(zone2.value) as zone2Avg,
avg(zone3.value) as zone3Avg,
zone1.FileSQN
FROM
(
SELECT
FileSQN,
CASE WHEN value <= 0 THEN NULL ELSE value END AS value
FROM Table1
WHERE Observation in (1,2,3,4,6)
AND DateDiff(day, ProcessEndDate, GETDATE()) <= 1)
)zone1
INNER JOIN
(
SELECT
FileSQN,
CASE WHEN value <= 0 THEN NULL ELSE value END AS value
FROM Table1
WHEREObservation in (5,8,11)
AND DateDiff(day, ProcessEndDate, GETDATE()) <= 1)
)zone2 on zone2.FileSQN = zone1.FileSQN
INNER JOIN
(
SELECT
FileSQN,
CASE WHEN  value <= 0 THEN NULL ELSE value END AS value
FROM Table1
WHERE Observation in (10,12,13,14,15)
AND DateDiff(day, ProcessEndDate, GETDATE()) <= 1)
)zone3 on zone3.FileSQN = zone2.FileSQN
GROUP BY FileSQN

The technique I ended up using, used the WITH common table expression:

WITH WithTable (FileSQN, value, ObservationNumber)
AS
(
SELECT
FileSQN,
CASE WHEN value <= 0 THEN NULL ELSE value END AS value,
ObservationNumber
FROMTable1
WHERE DateDiff(day, ProcessEndDate, GETDATE()) <= 1)
)

SELECT
zone1.zone1Avg,
zone2.zone2Avg,
zone3.zone3Avg,
FileSQN
FROM
(
SELECT
avg(value) as zone1Avg,
FileSQN
FROM
WithTable
WHERE ObservationNumber in (1,2,3,4,6)
GROUP BY FileSQN
) zone1
INNER JOIN
(
SELECT
avg(value) as zone2Avg,
FileSQN
FROM
WithTable
WHEREObservationNumber in (5,8,11)
GROUP BY FileSQN
)zone2 on zone2.FileSQN = zone1.FileSQN
INNER JOIN
(
SELECT
avg(value) as zone3Avg,
FileSQN
FROM
WithTable
WHEREObservationNumber in (10,12,13,14,15)
GROUP BY FileSQN
)


Summary: The first technique was slower, since I was basically pulling the same info 3 times, compared to the second technique's once.

Note: The names of the tables have been change to protect the innocent. I wrote this in line, so not everything might be syntactically correct

Reminder: How to use Window's Shell32 to do basic decompression (unzipping).

Add the shell32.dll reference found in C:\Windows\System32

Excerpt from my code:

Shell32.Shell sc = new Shell32.Shell();
Shell32.Folder SrcFlder = sc.NameSpace(file.FullName);
Shell32.Folder DestFlder = sc.NameSpace(args[2]);
Shell32.FolderItems items = SrcFlder.Items();
DestFlder.CopyHere(items, 20);

where:
file.FullName is a string representing the full path to the *.zip file. (Actually a FileInfo type)
arg[2] is another string representing the destination folder.