Impossible to run a SQL query - all applications hang 1

[sylro]

Hi, all!

I have a 4 GB database, let's say database DB1. DB1 keeps all recordings from the last 90 days and all the rest (starting 91 days ago to sometime in 2004) is transactional replicated to DB2 (30 GB), on the same server.

The problem is: if I try to run a SQL query on the DB2 (the historical database) all the applications running on the server operate very,very slow, still the CPU is not exceed 10 %. The same was happened when I stopped the replication.

What can I do to solve this situation?

Thanks in advance,
sylro

 

[SQLWilts]

What version of SQL are you runnig, and how much memory do you have on this server?
They are just 2 seperate DBs aren't they? Not 2 instances?

 

[sylro]

Indeed there are 2 different DBs on the same server. It's talk about a HP server with MS SQL Server 2005, RAM = 2 GB, enough for the necessary applications, related to databases.

For this moment, I can't see any solution to run a query on the DB2 (historical DB).
Waiting for your ideas...
sylro

 

[SQLWilts]

So are you saying that applications OTHER than SQL are running slowly?
If that is the case, then wind the max memory back to, say 1.5GB. This will leave 500MB (or there abouts - if you do the maths) for the op system

 

[mrdenny]

Sounds to me like your disk system is causing you a bottleneck. Take a look at your indexing on the archive database. If this database isn't indexed correctly queries which are very simple can require a large number of IO reads which will cause everything to slow down without the CPU going up.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[sylro]

Thanks for your interest!

But...
I made an indexes rebuild on the historical database -nothing was changed. And the memory level doesn't grow up when the command to execute a query is sent.

To SQLWilts: Indeed, all OTHER applications than SQL run very slowly.

Other ideas?

thanks,
sylro

 

[sylro]

I checked the Disk performance of the server and I remarked (as I expected) that "Disk reads" for the partition containing DB2 grows up to the maximum when I run the query. So the limitation is due to HDD. (The memory and CPU - no problem)
The problem became: how can I separate the process of querying and to "brake" it, thus it will operate under the speed of HDD.

All the best!

sylro

 

[sylro]

Some kind of priority for each process...

 

[mrdenny]

There isn't any way to assign priority for the processes.

You need to look at adding additional indexes to the tables in the DB2 database. You may need to purchase additional hard drive resources to correct this problem.

Start by taking the queries which are causing the problem (use SQL Profiler to identify the problem queries) and make sure that they are indexed correctly. If they are (all seeks, no index or table scans) then additional hard drives will be your answer. If they are not, create the additional indexes and try again. Once everything is indexed optimally if the problem still exists you now need to purchase additional hard drive resources. If the problem doesn't come back you are all done.

Is DB1 and DB2 on the same drive? What does the drive layout of the machine look like? Perhaps we can reconfigure the drive arrays of the machine to improve performance.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[sylro]

DB2 is on the other drive than DB1, specially introduced to keep DB2.
I used the 'dbcc indexdefrag' command for the biggest tables and the result is that the time during query running is shorter now but the problem remains - the applications are almost blocked till the query finish its job.

I'll look for other ways and I'll wait for other ideas from you.

Anyway, the next step is to check what services is blocked during the query.

Thanks, mrdenny

All the best
sylro

 

[mrdenny]

OK, but you need to see what indexes need to be added to the DB2 database. This is different that defraging the existing indexes using the DBCC INDEXDEFRAG command.

When the system slows down what does the disk queue look like? How about the seconds per read?

What drive letters do you have, and how many spindles are there for each drive on each RAID array? What drives is each database hosted on?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[sylro]

1.How do I know what indexes need to be added to DB2 database?
2.DB1.mdf is situated on first drive but DB1.ldf is on the same drive with DB2 (the second drive). The configuration is a RAID 1 (mirror). I intend to move DB1.ldf to the first HDD but it needs to interrupt the operators which work with database. Do you think this is a good idea? This may be the reason to 'fulfil' the drive reads?

All the best!
sylro

 

[mrdenny]

1. You need to do basic performance tuning on the server. Look at the poorly performing queries and look at the execution plans for those queries and see where SQL is doing index (clustered or non-clustered) or table scans. Those operations need index adjustments. Either changes to existing indexes or creation of new indexes.

If you have SQL 2005 you can look at the sys.dm_db_missing_index_details DMV.

If you don't know how to setup/correct the indexes manually you can use the tuning wizzard which comes with SQL Server. It's not perfect, but it's pretty good.

2. How are the disks physically laid out? Do you have a total of two physical disks and there are two volumes on that RAID array? Do you have two arrays which each volume being on it's own RAID array?

If you can write it out like this it will be easier to see what's going on.
c:\ RAID 1 (2 Disks) OS, DB1.mdf
d:\ RAID 1 (2 Disks) DB2.mdf (same disks as c:\)
e:\ RAID 5 (4 Disks) DB1.ldf, DB2.ldf

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[sylro]

A month ago I made a 'rebuild' for indexes of a biggest tables. This 'rebuild' is something like tuning wizzard do?

Indeed I have SQL2005Server but I didn't find yet the system table sys.dm_db_missing_index_details. In common case, where can I find it?

The situation with the drives (SCSI bus) is as follows:
c:\ RAID 1 (2 Disks) OS (Win2003 server)
d:\ RAID 1 (2 Disks) DB1.mdf (same disks as c:\)
e:\ RAID 0 (1 Disk) DB1.ldf, DB2.mdf, DB2.ldf (the second drive)

Today I moved DB1.ldf to d:\ (on the first drive) and the result was that when I was running the query on the DB2 the application was running slow to very slow, comparative with before when it was very,very slow.
As you said, I'll see what to do with the tuning wizzard regarding indexes. Anyway the indexes are the key of the problem. I'll keep you up-to-date with my progress.

Thanks for ideas and advices, Denny,you're a real professional!

All the best!
sylro

 

[mrdenny]

sys.dm_db_missing_index_details is actually a view. You probably won't be able to find it in the object explorer. You should be able to query it.

All rebuilding the indexes does is flush and completly recreate the existing indexes. It does not create any new indexes.

After you have created any missing indexes which are needed you will probably want to adding another RAID array for the DB2 database to sit on. Reporting databases typically need very fast hard drive configurations as reporting typically requires much more disk IO when the reports are being run than the OLTP application needs. This is because much more data is needed, and that data isn't queried very often so it isn't loaded into cache.

You should probably look into setting up another RAID array for the DB2 database. I'd recommend a RAID 5 array with at least 3 or 4 drives in it. Run perfmon and take a look at the disk queue and the operations per second of your E drive. If you add those two numbers together that's the number of operations that you need to be able to support at a minimum. The current operations per second of your E drive will probably be the max that the drive can handle, so take the total number and divide by the number that it's currently able to handle and that's the number that you need to be able to support.

Keep in mind that you will pretty much always have a disk queue at some point because as SQL is given more bandwidth to read more data faster it will request more data faster. You want to be able to satisfy as many requests without queueing as you can afford to do, without going broke in the process.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog