Posts Tagged ‘SQL Server’

What is that SPID doing?

March 27, 2006 Leave a comment

This is certainly not a new topic but something that i felt was worth revisiting and reminding people about, especially because i find many people are not aware of the ability to extract the statement within a stored procedure being executed.

So, prior to SQL 2000 SP3, when we want to know what code a SPID is executing we have to use DBCC INPUTBUFFER (). The problem with this command is that its restricted to the first 256 characters and only returns the outermost statement executing. That means that if we have stored procedure 1 calling stored procedure 2 the input buffer will only tell us about stored procedure 1 and it may even get truncated!

I clearly remember reading the SP3 readme and thinking Christmas has come early as i found the entry about a new function being introduced called ::fn_get_sql. I’ve got to say right now this is the best present yet! It has made trouble shooting so much easier. Why? Well read on.

Running the code below will now show you the inner most statement executing and we now get up to 4000 characters. In the case of a stored procedure you see the whole procedure.

SELECT @Handle = sql_handle FROM MASTER..sysprocesses WHERE spid = 109 --CHANGE SPID NUMBER
SELECT * FROM ::fn_get_sql(@Handle)

The part that really got me? When it returns the code of a stored procedure you could be left wondering what statement within the stored procedure is executing. Fear not! They have that covered to. All you have to do is run the modified code below and the exact statement causing you a problem is identified.

DECLARE @Handle BINARY(20), @start INT, @end INT
@Handle = sql_handle, @start = stmt_start, @end = stmt_end
FROM MASTER..sysprocesses WHERE sql_handle <> 0x00 AND spid = 226  --CHANGE SPID NUMBER
IF NOT EXISTS (SELECT * FROM ::fn_get_sql(@Handle))  PRINT 'Handle not found in cache'
'Current statement'=
SUBSTRING(TEXT, (@start + 2)/2,
CASE @end
ELSE (@end -@start + 2)/2

FROM ::fn_get_sql(@Handle)

So, now i’ve reminded you of this really useful tool its time for a few "Battle Tips".

1) Make sure in query analyser your "maximum characters per column" is set to 4000. Otherwise you are only going to get 256….
2) If  SQL generates a "zero cost plan" it will not put it in cache because its not worth it. As its not in cache ::fn_get_sql won’t return any information.
3) This one comes with a health warning….. If for some reason you need SQL to cache the zero cost plans so that ::fn_get_sql will always work then you can enable trace flag 2861. I would recommend you do not use this in production as the overhead is substantial and profiler should be used instead but some people might find it beneficial on a dev box.
4) I’ve not adjusted the script above to deal with parallel spids where you get multiple handles for 1 spid. Just a note, i keep on forgetting.  😀

There are many uses for this function and i encourage you to explore them. SQL 2005 makes life even easier through DMV’s but that’s another blog……

SQL 2005 – Log Shipping Monitoring?

March 12, 2006 Leave a comment

I took a look at log shipping in 2005 for the first time the other day and as i was curious to see how the monitor functionality had been implemented in SQL Server Management Studio i went hunting for it. I’ve got to say i was greeted with nothing but disappointment and left feeling that i now have a huge mountain to climb before it can be used in production!

We currently have 15 databases shipped to multiple destinations and in SQL 2000 we monitor and manage from a central monitor server. In Enterprise Manager there is a log shipping monitor node under management and any databases that have failed are marked with a red cross and we can right click and suppress the alerts whilst we investigate and the investigation often starts with a right click to view the relevant history for the database in question. Amongst other things we can also automatically suppress alerts for periods of time.

Now, what did i find in 2005? After a good hunt around i found a report that you get to by going to the summary page at the server level and choose "Transaction Log Shipping status". I then found a single job on the monitor server responsible for generating the alerts for all the databases. As for viewing the histories, its a case of find the jobs or run a query against the relevant monitor tables!

In SQL 2005, log shipping has been wrapped up into its own executable and divorced from maintenance plans. I was expecting to see a number of improvements over SQL 2000 or, as a minimum, be on the same level but i was not prepared to find that out of the box it was going to be far more difficult to use! Why? Because the monitor tool in 2000 was simple and with reasonable instruction anyone could use it. I now feel i need to write at least 1 more report and i need to come up with a way of suppressing individual alerts and introducing suppressions for certain time periods to meet our business needs and ensure its maintainable by people who are not DBA’s.

I just don’t understand why Microsoft have done this! Sad smile

Categories: SQL Server

2005 – Alter failed for server XXX

February 13, 2006 Leave a comment

That’s exactly what i thought!! What the hell does that mean?

Well, for me this occurred in the context of running the maintenance plan integrity check task. It had been running fine for weeks but suddenly boom every time it ran it would fail with

"Alter failed for server XXX".

There was no obvious reason for this error so i had to dig out profiler to see what was going on. It turns out that one of the first statements executed by the plan is "sp_configure ‘User Options’,xxxx" followed by "RECONFIGURE".  The reconfigure statement was then generating an error which caused my problem.  But why was it generating an error? That was because a script had been run on the server that changed the "Allow Updates" value from 0 to 1. Changing the value back to 0 resolved the problem.

BOL states that changing the Allow Updates option has no effect since you can’t update system tables in 2005 but it clearly does have an effect since the behaviour of reconfigure changes.

There’s a few points to be taken from this.

1) There actually is no benefit to changing "Allow Updates" so make sure you remove it from your scripts, i have.
2) Why was the "sp_configure ‘User Options’, xxxx" followed by "RECONFIGURE" being executed anyway? Its not relevant to integrity checks and why is it updating server wide settings? I noted the setting was simply set to what it already was, fortunately.
3) Bear this little gotcha in mind because who knows where you will first meet the problem and what message will you get?

Categories: Configuration, SQL Server

A performance analysis example

January 15, 2006 2 comments

Last week i was dragged kicking and screaming away from my allocated SQL 2005 days to spend 2 days diagnosing performance problems on one of the firms Systems and write a report.
Due to the sensitive nature of the data on the system it has been managed by another team and this was the first time i had looked at it. I thought i’d share my thought process to identifying the problem on the server and reference tools i used. Its the first iteration of the review and once my recommendations are implemented there will be another review to see what else may need dealing with. Please note however i have not included every single step i took otherwise i’d be writing a novel!


The users of the system have been reporting intermittent time outs occurring in the front end application. Current system administrators have noted periods of high cpu usage. The server is Windows 2000 SP4 running SQL 2000 SP3a with 2 physical processors, hyperthreading enabled, 2gb memory, SAN attached (EVA 5000).


The first port of call was to review the event logs and sql server logs for any errors that may indicate what is happening. These logs revealed nothing. Next port of call was to connect via Query Analyser and see what was going on. A quick look at current activity showed nothing much going on, so i did the standard SELECT * FROM SYSPROCESSES. Now this revealed that 80% of the connected spids last wait times had been for a latch and specifically the waitresource was 2:3:69 or 2:1:24.

Read more…

Categories: Performance, SQL Server

SQL 2005 – Maintenance Plans

January 3, 2006 Leave a comment

I made a decision that to really get things going and identify “trouble spots” that i would continue my quick win approach.

So basically i`m working through installing and configuring our test SQL 2005 box following our SQL 2000 install instructions. Through out this approach i`m documenting and making necessary changes so i can have first drafts of install docs which i`m sure will evolve into excellent documents by the end of the project Open-mouthed smile

So, having done the basic install which i talked about previously i decided lets tackle maintenance plans as there should not be to many surprises here………

I have got to say that the new maintenance plans are far more flexible than ever before with a lot of added functionality and over all i’d say its a job well done. However the new maintenance plans have inflicted much pain for me.

Read more…

Categories: SQL Server