simple wait states

Klaus Aschenbrenner has a simple overview of wait states.

Unless the query gets the cpu and finishes in 4 ms, all queries in MS SQL are at times waiting.

A query that is being processed has to yield its place by cooperative Scheduling, which is enforced for SQL processes by the SQLOS.  Every 4 milliseconds, the query is moved to the ‘suspended’ state to let other queries play.  Then ideally, it moves to the runnable state and waits until it it has another chance to play.

But at times, or even the first time around, it might not get on the CPU.  If the storage manager can not locate the data needed by the query, the query gets moved immediately to suspended until the I/O system can find the data on disk, read it into the data cache, and tell the storage manager that a pointer to the data can be given to the query.

A query might in the middle of its run need more data.  This could happen if some of the data was loaded but there is not enough room for all the data, and now the additional pages need to be loaded.  Or a subquery.  Or the Query analyzer’s plan needs to write something to tempdb (for example a sort).  Anything that is going to require reading from the very slow physical media is going to make the query wait.

Now, suppose the data is in memory, but another process is using it.  OK, wait 4ms and it will be come free.  But it will take time to request and get granted a lock on the data for our query – it has to wait for the resource to become free, and then get the lock.

There are other, non-normal, causes for waits that indicate something is not ideal, but the point is that wait states are normal.  And all wait state information is stored by SQL Server and can be examined by the Dynamic Management View sys.dm_os_wait_stats.  If the server is running OK, the wait states you see with this DMV are normal.  If the server slows down, the reason for the slowdown will be seen as one or more wait states.  The wait state is the symptom, not the cause (slow storage, bad indexes, poor plans, locking and blocking, CPU pressure) but will start you looking, hopefully in the right places.

simple foreach through a table

Select * into #temp from dbo.table

We know that one – now, how to do something for each row in the table?

consider this:

declare @column1 varchar(10)
select top 1 @column1 = column1 from #temp order by tableName
-- if there are no rows, @@rowcount is zero
while (@@rowcount > 0)
-- do something with the selection
print column1
-- delete the entire row we just selected
 delete from #temp where column1 = @column1
 select top 1 @column1 = column1 from #temp order by column1

What we are doing is selecting one row from #temp.  I am attempting to maintain the same order each time by the order by, but for the purposes of this post, it doesn’t matter.  We do something with the row data.  We delete from #temp that row we just selected.  We select another row from the diminished table.  Assuming @@rowcount is still positive, we do it all over again.    Because of the ‘while’ we have to do the first selection outside, and we have to check the new value of the loop at the bottom.  Or else after the last row is deleted, @@rowcount would be 1, we would go to the top and enter the loop again based on the last selection success.  Or actually, the last deletion.

email report

I’m writing a report and i want to use SSMS to send it. OK, use ‘msdb.dbo.sp_send_dbmail’ to send it from inside SSMS.

But i want it to be formatted. OK, set @body_format = ‘HTML’ and you can format it with HTML to your little hearts content.

But it looks so ugly. OK, but that is NOT my problem.

Setting up the format for the HTML typically means creating a number of nvarchar(max) local variables, stuffing strings into them and combining the strings into the value of the @body variable. The same stuff you would do with powershell, or C# or whatever. . The typical grunt work of

set @header = '<table border = ''1''><th>Column 1</th><th>...'

You need the double singlequotes to get the single quote around the border (like border = ‘1’)

A couple things will help. One is to find a nicely formatted table, and steal it. It will probably have CSS in it, so be prepared to have something like this:

SET @tableHTML =
 N'<style type="text/css">
 font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
 font-size: 12px;
 text-align: center;
 #box-table th
 font-size: 13px;
 font-weight: normal;
 background: #b9c9fe;
 color: #039;
 #box-table td
 color: #669;
 tr:nth-child(odd) { background-color:#eee; }
 tr:nth-child(even) { background-color:#fff; }
 </style>' + 
 N'<H3>'<font color="red">All Rows From [db].[table].[column]</H3>' +
 N'<table id="box-table"> ' + ...

OK, so a standard style sheet at the top, you can fuss with these after it’s working. Next a <H3> Title outside the table, then the top of the table. Start building your columns. You COULD use sys.columns to get a list of all of the table columns, and you could put in a != condition if we want to exclude a column from the report.

Next, we want the data for each column, we want it tagged without having to manually put the </td><td> in a bazillion times, and we want it readable. Sounds like XML to me.

try something like this:

+ CAST ( (
 SELECT td = CAST([ID] AS VARCHAR(100)),'',
 td = [column1_name],'',
 td = [column2_name],'',
 td = [column3_name] ,'',
 td = CONVERT(VARCHAR(30),[StartDate],120) ,'',
 td = CONVERT(VARCHAR(30),[EndDate],120)
 FROM [table]
 ORDER BY [startDate]
 N'</table > '

Cast the selections, each aliased to ‘td’ – but that’s ok, because we are selecting FOR XML. Notice you close the table in the last line, or you can have a htmlTail variable as well and glue them together.

Another option would be to have one or more of the td elements something else, like tdc. This will output XML with an invalid HTML element, however you can then do something like this to put special formatting in that/those cell(s).

SET @body = REPLACE(@body, '<tdc>', '<td class="center">')

can’t find the database if you are standing in it

I must have spend about 2 hours on this one.  I had a stored procedure that i was restructuring.  I was changing from one database to another, but both on the same server.  I got it to work in Data_1, but not Data_2.  I THOUGHT it was the same code, but on Data_2  i kept getting an error that the sys.servers could not find Data_2.  I could rebuild the process from the beginning, but as the logic got more complex, i would lost Data_2.  Since i could make it work, it was not the problem, except that i wanted to find the cause.  I tried to compare the two stored procedures in notepad++ with ‘move to other view’ on the second file tab, but i was not finding the problems.  And with usp, you need to both EXECUTE the usp to save it, and then exec it to run it.  Eventually, it would stop working and i was never confident of when it failed.

OK, so the answer is in the error.  I can reference [myTable] or [dbo].[myTable] or [databaseName].[dbo].[myTable] and they all should work.  What i can not do is this:


for a database on the same server, however i can do this:


Can you see where i am going?  The logic was complex enough that i had resorted to some limited copy\paste at times, and i had accidentally moved code like this


which looks like a four-part name to the parser, which complained it could not find [databaseName] — as a SERVER.

I hope it doesn’t take me two hours to remember this next time.

replace and convert

There are a couple of points in the stuff below.  One is the value of having sensible names.  Perfectly nice people put spaces into file names or urls.  Or mix numbers and letters (like putting a ‘z’ in from of something to indicate not to use that number.)  Just because they can.  There then is need to deal with such-like.  Or make EVERYTHING varchar(max) And a quick but dirty(?) way to create a numbers table.  This one i can even remember.

From Simon Sabin – he wants to parse data into into urls, but the data contains spaces and other naughty bits – which means URL encoding (space becomes %20, et al).  What he does is select the data (which are session titles) and  replace spaces with an underscore character:  “big cat” -> ‘big_cat’.

select cast(cast((
select case when substring(title,n,1) like '[a-z0-9/-]' 
then substring(title,n,1) else '_' end
 from num
 where n <= len(title)
 for xml path('')) as xml) as varchar(max))

So get the substring of one character, check to see if it is appropriate (is this all lowercase?) , if so select it, if not replace with ‘_’.  This assumes a numbers table/tally table of your choice.

From  Derek Dieter,  he has some strings that might contain non-numeric things (123A456) and he was to get rid of the ‘A’ and convert 123456 into an integer.  Similar to the above, he does this

 WHEN SUBSTRING(field,Num,1) LIKE '[0-9]' 
 THEN SUBSTRING(field,Num,1) 
 FROM Numbers
 WHERE Num <= LEN(field)
 FOR XML PATH('')  ) AS INT  )
FROM inputTable

It depends on how long the field is if the maxrecursion is needed – the default is 100 and this applies since we are using a CTE.

The rest of the code is generating the numbers table.  He does that like this

DECLARE @MaxNumber INT = 5000
;WITH Numbers AS
    SELECT 1 AS Num 
    SELECT Num+1 
    FROM Numbers 
    WHERE Num <= @MaxNumber

Select 1, union all and do it again with select Num + 1.  Repeat until cooked.  Actually, repeat as many of 32767 numbers.  As above for the default recursion of 100, this will not work with @maxNumber = 500.  It will work with 100, or you can set the OPTION(MAXRECURSION 5000) .  Or leave it at 32767 and don’t worry until it gives you an error

The statement terminated. The maximum recursion 32767 has been exhausted before statement completion.

identity column

I am instructed to add an identity column to rows of data in a table.  A simple 1, 2, 3 integer would be fine, and of course, when information is inserted, you don’t specify the identity and it will bee automatically added to the table.  Something like

create table myTable (
 id int identity(1,1)  not null,
 , myData varchar(10)
Insert into myTable (myData) "abc"

this results in 1, “abc” in the table

But,  you say “i want to create the table from a select into, NOT an insert?  Now what?

I got this from  on

 ,FirstName = pc.FirstName
 ,LastName = pc.LastName
INTO #person_contact  FROM Person.Contact pc

You are getting the first and last name into the (in this case) temp table and adding an identify column on the fly.  Notice that subsequent inserts of first and last name will automatically add the id entry, which is of type int, started with 1 and increments by 1 on each row and for each insert. You can also use row_number() over…  like this

    ,FirstName  = pc.FirstName
    ,LastName   = pc.LastName
INTO #person_contact2 FROM Person.Contact pc

66535, but who’s counting?

If you want to look at the description of a view (and that is the field name where in the selection code is stored) you can select the view, open DESIGN and see the code that selects the columns, with all the alias and the joins and froms, oh my.  Maybe

We have some views where that just doesn’t work the way you want.  You can see it, but when I try to select all\copy\paste into a text editor, and all the stuff doesn’t come over.  OK, so i put the cursor in from of the first word, and use the down arrow to select the text, REBAR style.  At some point, the “copy” select goes off and you can’t select the text.  A couple of times, i have been able to copy half and then select the rest, but there are times when that doesn’t work either.

So – how about this:

EXEC sp_helptext 'dbo.myView';

Yup, that works, but in my case, i get 1049 rows of text of varying lenghts.  REALLY ugly.

How about this:

USE [Ntier_MIPU]
SELECT definition,*
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(‘PM.vwGenPatInfo’);

you get the object_id, the definition field, which contains selecta view myView as select … but in my case, the definition is truncated.  There is a character limit for how long the field can be in grid-view.  I had no better (in fact, worse) luck with text view and exactly the same data if i output it to file.

In  Management Studio – Tools, Options, Query Results, SQL Server, Results To Grid, Maximum Characters Retrieved – Non XML Data, there is a setting for the Maximum Characters Retrieved, which can not be set to more than 64K.  You can of course select more data to a table, but the display is limited to the aforementioned 66535 characters.

And for XML RAW was similarly ugly and not terribly readable.  Maybe i could have opened it in MS Internet Explorer or such like with a style sheet that would not show the elements and attributes but that was WAY too much work, given the ease of my workaround.

If you want to build, or duplicate, a big table — i mean a table with a complex schema — you can right-click on the table in SSMS, Script Table as, create to … whatever (file, agent, query window).  Yes, query window.  So find the view, script to create in the query window, trim off the create view front stuff and the closing quote, and you have it.