SQL Server Performance Forum – Threads Archive
Urgent: Sql Performance.
Dear All, I’ve been running into the strange problem since a month. Sometimes, an stored procedure seems to fail. It is not specific to only one SP though, sometimes a view that is fetching data seems to run longer than normal. One big problem I see is that, there are four tables that contain huge data including image binary data. and When I see the index relationship it was like there are two or three indexes were there and on primary key index, it was set on unique, not as clustered index. However there was other indexes on tables that contains the rest of the columns excluding the primary column and they were set on "Create cluster" . I unselected this and things work fine for few days then again, things seem to having problems again and CPU gets high when running on simple SP. However that happens with random SP or views. My database size is 22 GB and log file and mdf file are on the same partition along with other databases.SQl 2000 with SP4
Windows 2003 Server
Although i’m working on archiving process and already archived 3 GB of data.
The clustered index is "repeated" on the nonclustered indexes, eventhough it’s not presented as such in the index definition. The PK doesn’t have to be clustered. The ideal candidate for a clustered index is an identity column, because new values never have to be inserted between existing values. The official line about clustered indexes is that you should use them for columns on which you often do range searches, but I think that the true benefit is in maximum efficiency for the nonclustered indexes.
so Should I put the PK off from "as clustered" ?
What kind of data do you have in the PK column?
Only Ids for that table
Is it defined as an identity column? Then make it clustered.
yes identity column.
Getting random error. General Network error. Check your documentation. Sometimes works sometimes doesnt.
Might be a hardware issue.
Named pipes issue?
Are you doing this from a remote computer?
YES
]]>