November 23, 2015 at 2:23 am
Hi,
I have a table having 166 cols, and 7380000 records many indexes. index space 13 GB, Disk space 5 GB. no trigger.
suddenly, in past couple of days some time if i add record using
insert into a (c1,c2) values ('a',1)
query, no error coming & query executes for long time...
why this happening i can even guese.
ls help
its urgemnt
November 23, 2015 at 2:36 am
Look up sp_whoisactive, create it on your server, then use it to see what's happening while your INSERT statement is running.
John
November 23, 2015 at 2:37 am
Does it succeed after some time?, Can you find your session sys.dm_exec_requests? what is the status and waittype ?
November 23, 2015 at 3:51 am
its a live server so i can't wait for more then a minute.
when i run 'select * from sys.dm_exec_requests '
one of row is
Status = suspended
command = INSERT
wait resource = 7:1:4791127
test_size =2147483647
wait_Type = PAGEIOLATCH_EX
last_wait_Type = PAGEIOLATCH_EX
November 23, 2015 at 4:01 am
If you're getting PAGEIOLATCH_EX waits then that's often an indicator of problems on your disk. Is your disk shared with any other applications, file shares or other SQL Server instances? Get your platforms admin to run a check on it - maybe there are faults on it, or perhaps you don't have your production databases on the fastest storage? You say you have many indexes - how many? It sounds like you may have too many for an OLTP system. You can use the index DMVs to find out which of the indexes are actually being used.
John
November 23, 2015 at 4:04 am
thenx for help,
but dear i can't run exec sp_whoisactive.
i am using SS2k8
November 23, 2015 at 4:10 am
i got about 60 indexes in this table
DMVs ?
what is it ?
kindly guide me. pls
November 23, 2015 at 4:23 am
KcV (11/23/2015)
i got about 60 indexes in this tableDMVs ?
what is it ?
kindly guide me. pls
Dynamic management views. Here's one for index usage. If I may say so, it sounds as if you're in a little over your head and you ought to get some help on this, especially given that it's affecting production.
but dear i can't run exec sp_whoisactive.
i am using SS2k8
Yes you can. Search for it, create it, run it. I promise it'll work.
John
November 23, 2015 at 4:50 am
You have alredy used one of DVM above.
Conserning possible disk problems see scripts in http://www.databasejournal.com/features/mssql/finding-the-source-of-your-sql-server-io.html
November 23, 2015 at 5:39 am
Thanks 🙂
November 25, 2015 at 1:53 pm
You mentioned you have tons of indexes. I hope on of them is a clustered? Lots of indexes ( lets say half the number of columns on the table) can degrade insert performance.
----------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply