Slow performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Slow performance

Author

Topic

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2007-07-11 : 23:20:03

Our SQL server performances very slowly from time to time.
I check the Locks/Process ID, it displays that some users are block by themselves
e.g. spid 68 (blocked by 68)

Why will it happen? Dead lock to himself?

More, the objects/tables seems to use wrong index to search.
e.g. table a seems to use table b's index

Why will it happen too? How can I solve this?

Thx

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-11 : 23:34:40

You prbly need to hire someone...
If you had to debug..you could start identifying the queries that are blocking each other. Do a DBCC INPUTBUFFER(spid) when you see blocking. Use NOLOCK on SELECTs if you can.
Do you mean queries are using wrong indexes?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2007-07-12 : 00:04:53

I don't know.
For example, the spid 65 lists five objects.
however, the field "index" display something not relate to the objects listed.
Anything wrong?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-12 : 00:09:46

when you do a DBCC INPUTBUFFER(65) (or whatever the spid is) what does it show?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2007-07-12 : 00:20:04

sth like this

Language Event 0 Exec Agent_PolicySummary @AgentNum = '271390', @PolNum = '000102485747'

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-12 : 11:27:57

Identify who is blocking who? when you do sp_who2 you see a column "Blkby". Identify the spid in that column and do a DBCC INPUTBUFFER(spid) for that spid. That spid is blockin others. Then look into the proc to see whats happening in it.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2007-07-12 : 11:40:26

Blocking is actually very normal. Its there because sql server keeps database consistent with there help. But if it is for a longer period of time then you need check why is it so. Completly agree with Dinakar. Find the culprit and fix the code.

Moreover you can also look health of the index by "DBCC SHOWCONTIG". Defrag the indexes if they are fragmented.

Thanks
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2007-07-16 : 01:05:23

I tried the command and it lists all tables.
How can I interpret the data? Which command to defrag the index?

DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 23
- Extents Scanned..............................: 7
- Extent Switches..............................: 21
- Avg. Pages per Extent........................: 3.3
- Scan Density [Best Count:Actual Count].......: 13.64% [3:22]
- Logical Scan Fragmentation ..................: 43.48%
- Extent Scan Fragmentation ...................: 71.43%
- Avg. Bytes Free per Page.....................: 2705.9
- Avg. Page Density (full).....................: 66.57%

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-16 : 22:46:08

Scan Density should close to 100%, and you can defrag table by rebuilding clustered index.

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2007-07-17 : 01:58:07

I already defrag the other user tables as you suggested.
however, if i found that the scan Density is very low in sys* tables. How can i defrag?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-17 : 13:24:14

You can't do anything on system tables. By the way, sql uses shared extents for small tables. That's why they have low scan density.