SQL keeps hanging, web apps just freeze

[1DMF]

Hi,

I am starting to get lots of complaints from our memebrs who use our extranet services.

The perl program when trying to connect to SQL just hangs.

The only way I can resolve the issue is by stopping SQL services and starting them again, killing all connections to the server.

This has only started happening the last few weeks, what could be causing it?

How do I investigate why SQL is failing to respond to connection requests from the perl CGI application.

There doesn't seem to be anything in the event viewer, I've monitored the task manager 'performance' no spikes or excessive usage (CPU @ 20%)

Your advice is appreciated.

1DMF



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[SQLBill]

Check out the msdb.dbo.syssubsystems table. This table controls how many 'threads' can be running at one time. If the amount is too low, then processes can start to hang.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[djj55]

One other thing to possibly look into, is the max memory for your SQL Server. Make sure you have it set to a reasonable value not the default.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[1DMF]

Bill ->

What subsystems thread value needs changing?

What is a good value?

They are currently....

TSQL : 40
ActiveScripting : 20
CmdExec : 20
Snapshot : 200
LogReader : 50
Distribution : 200
Merge : 200
QueueReader : 200
ANALYSISQUERY : 200
ANALYSISCOMMAND : 200
SSIS : 200

DJJ55 ->

It was set to a ridiculous number? 12345465345 MB

The server only has 2048 MB! I changed it to 1024MB Is this a fair setting for a server with only 2GB of RAM that is also the IIS webserver? I have ticked the box to boost SQL priority, is this OK?

Your advice is much appreciated.

1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[SQLBill]

Look at the TSQL setting....do you only have 40 queries at the same time? There isn't a 'hard and fast' rule for how those should be set...it's more of a 'tweek as you go'.

Try setting the TSQL one to 100 and see what happens.

You can run sp_who2 to see how many connections you have at one time and work with that. Remember though, some connections may not be active...they can be finished but not yet closed out.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[1DMF]

Well I changed it to 200 but it didn't seem to make a difference, so i upped it to 500.

I rebooted the server yesterday and it still seems sluggish at times, so i uped the Max server memory to 2048 and also set the minimum memory per query to 512kb , it was 1024kb.

i also turned on query govenor and set to 300 seconds.

It seems a bit perkier now, I'll let you know how it fairs.

Though I have finally got the go ahead for the upgrade / migration so this current server hasn't got to last much longer!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[1DMF]

We are still getting freezes.

I just ran an SP_Who2 , there are 53 rows returned.

The highest any of the CPUtimes are showing is : 2642

The server support team syggest it is related to an MS update... So I'm off to have a look!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[gmmastros]

53 rows isn't necessarily a problem. Any spid less than 50 is internal to SQL Server. When you run sp_who2, do you see anything in the BlkBy column?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Well I think it could be an update that has screwed SQL and it also doesn't have a remove option....

http://support.microsoft.com/?kbid=2597112

http://technet.microsoft.com/en-us/security/bulletin/ms12-027

Looks like they have force an un-uninstallable update affecting SQL2005 which I installed on 11.04.2012, which ties in with when this bizzarre behaviour started happening!

Now what?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[1DMF]

Also I noticed this update according to the technet info is for SQL2005 with SP4 ?

We didn't have SP4 installed (wasn't showing up as an update in my ESET AV!)

So why has MS installed an update to SQL for a specific SP that isn't isntalled?

I'm now installing SP4 - let's hope this resolves the issues.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[1DMF]

George ->

Yes there are loads of records with something in that column...


79
76
.
79
79
.
.
.
79
.
.
.
.
75
64
64
76
79
79
79
79
79
79
.
79
79

what does that mean?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[gmmastros]

The numbers you see are SPID's. What this means is that some queries are being blocked by the query that SPID 79 is running.

For example, suppose you had a database of people. You could run a query that updates the eye color while another query is running to get the shoe size. The update query will lock the data while it is updating, which effectively blocks the select query from running. The select query must wait until the update is finished before it can continue.

Since SPID 79 is showing up in many rows, I can only assume that it must be a long running query that is doing an insert, update or delete. If this query takes 5 minutes to finish, then all of the other queries must wait that long too before they can run. So, a simple select query could appear to take forever to complete.

My advice to you is to determine the query that SPID 79 is running and then optimize it to make it faster. If the query is updating a ton of data, then you may want to break it up so that it updates in batches.

You see... when SQL Server locks data, it actually locks it a "page" at a time. A page is 8kb of data. Therefore, it's possible that you can be blocked from retrieving data even for rows that are not getting updated if those rows share the same data page. Sometimes SQL will take a table lock if you are updating a lot of data. This effectively blocks the entire table from other select queries.

By optimizing the slow query to make it faster, you will reduce your blocking and the entire system will operate smoother/faster.

To see the query that is running...

DBCC INPUTBUFFER(79)

There are ways to write your queries so that they ignore blocking. Doing that is usually a bad idea and should only be used as a last resort. The problem with this approach is that you could end up getting data that is in the process of changing or even being deleted.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Thanks George, I was just reading up on this via

http://technet.microsoft.com/en-us/library/cc966540.aspx

At least I'm narrowing down where the problem might lie, once I have isolated the query causing the problem , then I can look into fixing it!

Everything has unlocked now , so I will have to wait until the next time it happens so i can investigate further.

I know the DB is poorly designed, so that is going to be part of the equasion, it certainly isn't in 2nd, 3rd or any other normal form :-(

Though refactoring a DB schema isn't a valid option is it?

There is countless lines of code throughout countless application that would break, and require all their queries re-written with the extra joins as refactoring would move data to new tables.

It's never a good idea taking a badly designed Access database and just moving it to SQL, but at the time, that's what the professional we had come in to do the move did, so that's what we have!

Plus there have been years of poorly designed additions to the system, compounding the problem.

Oh well at least we are getting somewhere now and hopefully I will be able to fix the current SQL lockups.

fingers crossed!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[1DMF]

OK, i have found the query locking things up but I don't know what to do about it?

It's not really a query, it's a view!

I have a form in Access bound to a table view, when that view is accessed it is locking out the tables that make up that view.

How do I resolve this?

I have also found that long after all records in the view are returned, it is still locking out other simple updates for ages.

Why when a query finishes it still locks the SQL DB?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[gmmastros]

Believe me when I say, "I know what you mean". I sell commercial software in a VERY niche market. You would probably assume that my database is in perfect condition (given the quality and amount of advice I have given over the years). The truth is (and this is a secret), I have certain tables in my database that are not properly normalized either.

Just yesterday I was looking in to adding some functionality to my app. This requires adding a small lookup table and a foreign key column to one of my main tables. Because of the nature of the change (which sounds really simple on paper), it will require me to look at approximately 640 stored procedure and maybe a dozen UDF's.

As systems grow over the years, it becomes more difficult to change them because of various dependencies. I am very well aware of this problem.

I wasn't necessarily suggesting that you go "cowboy style" and start changing your database structure. A properly designed database often leads to simpler queries, and simpler queries often lead to better performance.

I was suggesting that you take a look at the one query that is causing the blocks and then optimize that one query so that it performs exactly the same function but does it in a way the executes faster.

This may mean that you need to re-write the query using sargable where clauses and joins, and perhaps you will need to add additional indexes too.

Chances are, this offending stored procedure will show up in the list of slow running queries (highest average CPU time). You can get a list of these queries by running the code you find here:

http://wiki.lessthandot.com/index.p...th_the_highest_average_CPU_time_in_SQL_Server

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

OK, I'm getting very confused by these lockups we are experiencing.

61   	SUSPENDED                     	sa	     CL-PC  	  .
62   	sleeping                      	sa	     JV         	  .
63   	sleeping                      	sa	     CL-PC  	  .
64   	SUSPENDED                     	Members	BL-01 	61   
65   	SUSPENDED                     	Members	BL-01 	64   
66   	sleeping                      	sa	     CT   	  .
67   	sleeping                      	sa	     JV         	  .
68   	sleeping                      	sa	     CL-PC  	  .
69   	sleeping                      	sa	     AW  	  .
70   	SUSPENDED                     	Members	BL-01 	65

The SPID locking everyone out is 61, but that person had a screen that wasn't doing anything, it even shows their process as suspended.

So why is a user who is suspended and not doing anything locking out eveyone else?

How do I set the table view with no locks?

It seems the users is openeing every application we have which points to the same tables, and although their screens are just sitting there not actually doing any select or process, it's locking the entire system up.

I need a way of telling SQL to stop locking tables that don't neeed locking because this user is the office manager and wont stop opening every application all at once!

and why was SPID 61 suspended when there was no process blocking them?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[1DMF]

OK, in desparation I have changed every view and every stored procedure to use WITH (NOLOCK).

The majority of screens are really just datasheet views showing general data.

Only when a particular record is selected and the 'editable' screen opens on one paticular record does it need to see 'clean' data and allow edit plus lock record (not table)

I have also amended all access forms as I also hadn't set on some the recordset type to be 'snapshot' and they were still on dynaset with 'allow edit'.

Though the form 'record lock' was set to no, but clearly that doesn't work with SQL backend!

Any other advice you can give?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Downloads

 

[SQLBill]

About the suspended SPIDS, it is possible one of the queries being run does not close out the transaction. Running this will show you open transactions.

SELECT DISTINCT spid, blocked, loginame, hostname, last_batch, db_name(dbid) AS [Database_Name],
FROM master.dbo.sysprocesses
WHERE open_tran > 0
ORDER BY spid ASC
GO

Then use DBCC INPUTBUFFER (spid) to show what code is being run.

You can always KILL the spid to close out the connection.
-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[1DMF]

I just wanted to say a big thank you to all those who have helped with this.

Since the changes to the SP's and having given it a couple of weeks to see how things have faired, we havn't had a single lockup nor have I had any complaints that systems aren't working correctly or that they are seing potentially 'dirty' data.

Ok , possibly tempting fate with this post, but I think (touch wood) we have cracked the problem!

Again, many thanks for your invaluable help



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download