SQL Server Freezing

[jdukarm]

We have a freezing/pausing issue on our current SQL Server Standard Edition database. The database is 14gb in size and has about 80 users logging in. Is this a big database for Microsoft SQL Server to handle? The current server is dual Xeon processor 3.6GHZ, 2GB RAM, Windows 2003 Server Standard with 1gb network card. Have tried a whole host of things to stop the freezing, but now wondering whether we need to move to enterprise version?

Thanks in advance.

John

 

[SQLBill]

Is this a big database for Microsoft SQL Server to handle?

No. I have a SQL Server database over 200 GB in size and don't have this 'freezing'. (I am using Enterprise Edition, but it shouldn't make a difference).

How many concurrent users does your setup allow?

What kind of activity is occuring on the database?

In Enterprise Manager, drill down to Management, then expand Current Activity. Click on Locks/Processes. Are there any blocks? If so, match up the SPID to the information in Process Info.

-SQLBill


Posting advice: FAQ481-4875

 

[jdukarm]

Just recently we have had considerable slow down as the database has grown. Yes we get the odd lock etc but nothing as drastic as this. We get random freezing with our SQL Server and CRM tool. It is completely random. Working with my CRM tool provider we have looked at profiler it doesnt seem to be doing anything out of the ordinary.

Have anti-virus software running on the server, I know in the past anti virus software causes a lot of problems? Could it be that? We have moved it to another server and we still have the issue? What are the common causes of freezing in SQL Server?

Thanks for your help.

John

 

[SQLBill]

You say your database has grown and this problem started. I would look at your queries. Are they using indexed columns? If not, that's your problem. With a small database, using un-indexed columns won't cause much of a problem. But as a database grows, using un-indexed columns can cause queries to slow down drastically.

Example: Let's say you have to look up someone's phone number. You have a list of names and numbers on a personal phone list that is two pages long. The names and numbers are not in any special order (un-indexed). How long will it take to find a specific name and phone number? Not long. Now let's say that you have to find the same information in your city's phone book. But the phone book you have (with millions of names and numbers) is not indexed either. How long will it take to find the name and number you need? Quite a long time.

This may not be the issue, but it's a place to start looking.

-SQLBill

Posting advice: FAQ481-4875

 

[jdukarm]

Thanks have questioned my CRM developers and they assure me all indexes are fine and maximised. It wasnt the anti virus removed that and we still have the issue so i have installed again. I have high average disk queue length on Performance monitor maximum at 148 could this be an issue?