Poor Query Performance

[hjohnson]

I'm still fairly new to SQL-server, so if the answer is of the 'DUH' type, I can take it.

My problem is that every time I run a query - whether from query analyzer or some other source, our entire network really feels the drag. This is a development type of enviroment and this is the only SQL-server process that is running. The same server is also our fileserver, but not a huge demand placed on it.

What I've got is a basically a Datawarehouse and the queries that run against it are simple ones. For Example:
"Select TranID, count(*) from trans group by TranID"
There is a index on TranID. There is about 5.7 million records and only about 6 unique TranID values. This query takes about 1 minute to execute and while it's running, the the Avg.Disk Queue length in the performance monitor hits about 80 or 90. The server is 4 CPU (2.7ghz) with 3.5g ram. Any suggestions would be greatly appreciated.

Thanks in advance,

Hal

 

[JohnDTampaBay]

Run it in Query Analyzer and look at the execution plan. Verify that the index on TranID is being used. Is it a clustered or nonclustered index? If nonclustered, is there another clustered index on the table?

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[JayKusch]

Instead of letting SQL Server manage the memory dynamically, why don't you try to set a max amount it may use. If you have 3.5GB of ram, try allocating 2.75GB to SQL Server and then the rest of the server can work w/ the 750MB that is not being used. You may want to play w/ the allocations to see which numbers perform best for both parties.

The way you change it is my going into Enterprise Manager. From there, right-click on the server ... choose Properties then Memory. Change the radio button from "Dynamic ..." to "Used fixed ..." and set the slider bar to the first test setting.

Run a few of your queries and see the results. Once again, use the slider bar to adjust the settings of fixed memory and see what works best!

Thanks

J. Kusch

 

[hjohnson]

Angel,

There are no clustered indexes on table. The Execution plan is using the TranID Index. It shows a cost of 82%.

Would a clustered index on TranID be better. There is usually a lot of inserts/Deletes that take place so my thinking was that it would be better to stay with unclustered indexes.

Thanks

Hal

 

[JohnDTampaBay]

When was the last time the index was defragmented? Check out DBCC INDEXDEFRAG in BOL for some help there. What is the extra 18% of the query cost allocated to? I get 100% for the index scan on a similar table when I run a similar query. Jay may be on to something with the memory usage.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[hjohnson]

The other 18% is for the "Stream Aggregate" . There are 2 listings for this one with 0% and the other with 18%. How do you Defrag an Index? The performance monitor shows Memory and CPU barely even being used. I've adjust the memory as Jay mentioned without much difference in the results.

Thanks

Hal

 

[JohnDTampaBay]

Check out DBCC INDEXDEFRAG in BOL for some help on index defragmentation. I'll think on this and try to come up with some other possibilities.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[hjohnson]

Thanks for all your help