Query Tuning -System Waits - SQL

This query, from http://www.amazon.co.uk/Inside-Microsoft-SQL-Server-2005/dp/0735623139 obtains either the top 90% or at least 10 waits in the system:


WITH Waits AS
(
SELECT
wait_type,
wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%')
SELECT
W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 90
OR W1.rn <= 10
ORDER BY W1.rn;GO


To reset the statistics prior to undertaking analysis:


DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

String manipulation - removing extraneous detail - SQL

I'm currently working on an Integration Services project where I'm pulling filenames from a directory into a database to check for name consistency against a 'master' table.

The filenames take the form:
Abbey Lane Cemetery 03022 Floor Plan
Abbey Lane Primary 4-11 2001 Floor Plan
Abbeydale Grange Secondary 11-16 4254 Floor Plan
Abbeydale Industrial Hamlet 00433 Floor Plan
Aldine House CYP0048 Floor Plan

To check against the 'master' table, the entries need to be in this format:
Abbey Lane Cemetery
Abbey Lane Primary 4-11
Abbeydale Grange Secondary 11-16
Abbeydale Industrial Hamlet
Aldine House

What I needed to do was strip out the 4fig., 5fig. or CYP#### and the words 'Floor Plan' from every entry.

To strip out the words 'Floor Plan' would be simple enough, but was unsure how best to remove the reference number in each case. I first thought that I could create a selection for all entries with 4fig. and remove those, and repeat for all those with 5fig. and again for CYP####.
After much help from Chris Morris on SQLServerCentral.Com, he devised a query that would reverse the text string, find the position of the third space and return only the site name as required.
I combined the query with my Nums CTE to produce:


DECLARE @n AS BIGINT;
SET @n = 100;
WITH Nums AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Nums WHERE n < @n
)
UPDATE CADList
SET FileNM = d.[Output]
FROM CADlist AS c
CROSS APPLY
(SELECT LEFT(c.FileNM, LEN(c.FileNM) - MAX(x.n)) AS [Output]
FROM (SELECT TOP 3 n
FROM Nums AS n
WHERE SUBSTRING(LTRIM(REVERSE(c.FileNM)), n, 1) = ' '
AND n < LEN(c.FileNM)
ORDER BY n) AS x
) AS d


This can be improved by using the more efficient CTE as posted http://sqlreportingservicescrystalreports.blogspot.com/2009/12/auxilary-cte-of-numbers-sql.html
In my case, I'm only dealing with around 400 rows.

Auxiliary CTE of numbers - SQL

Following on from the previous auxilary CTE of numbers, http://sqlreportingservicescrystalreports.blogspot.com/2009/12/create-common-table-expression-of.html
I realised after reading T-SQL Querying by Itzik Ben-Gan that he's created a much more efficient way of doing it:


DECLARE @n AS BIGINT;
SET @n = 100;
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;





taken from:
http://www.amazon.co.uk/Inside-Microsoft-SQL-Server-2005/dp/0735623139

Create Common Table Expression of numbers - SQL

To create a Common Table Expression (CTE) of numbers for use in queries:


DECLARE @n AS INT;
SET @n = 100;
WITH Nums AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Nums WHERE n < @n
)
SELECT * FROM Nums

Create table code for testing purposes - SQL

To create and populate a test table from existing data for use in forums and for testing purposes:

IF OBJECT_ID('TempDB..#testtable','U') IS NOT NULL
DROP TABLE #testtable
----
CREATE TABLE.........
----

Create test data:

SELECT 'SELECT '
+ QUOTENAME(column1,'''')+','
+ QUOTENAME(column2,'''')+','
+ QUOTENAME(column3,'''')
+ ' UNION ALL' FROM existingdatatable

Run the above select statement and copy the resultant output (remove final UNION) into:

SET IDENTITY_INSERT #testtable ON
----
INSERT INTO #testtable(column1, column2, column3)
SELECT.....
----
SET IDENTITY_INSERT #testtable ON

Find and replace - SQL

UPDATE tablename
SET columnname = Replace(columnname, 'Find value', 'Replace value')