| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2009-01-07 : 11:08:09
|
| Okay, so i'm trying i recently added a SAN to my network, (8) 1TB hard disks on a stripe array.i've dropped all the indexes that were on the local disk with the intent to re-set them and partitioning up on the SAN. i added a clustered index on the san (it's probably a good 300gb) and now i'm trying to drop it and it's taking 11hrs, 32 minutes so far.in the activity monitor i've noticed a CXPACKET lock, and a periodic PAGEIOLATCH_SH / _EX. from what i read, alot of times things like this are due to a poor, or misconfigured subsystem. did i set something up wrong? or is it something else?i'm so frusterated i'm about to curl my self up into a ball and cry :( please help! |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2009-01-07 : 11:13:19
|
yeah, i also just noticed i put 100hrs in the thread topic... OPPS!small update, looks like i'm getting about 48.59 Mbytes per second thoughput on the SAN per an FTP xfer i did w/ filezilla.Also, i'm noticing the CPU load is @ 100% i'm no scientist, but that's probably not good  any ideas? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2009-01-07 : 14:53:02
|
| dont know how to check blocking, so pointers plz, as far as the recovery, it's simple the index drop is the only thing running on the server atm, nothing else and the SAN is dedicated to the the sql server. the server has 3 nics, 1 to the lan, and 2 intel pro1000's teamed for 802.3ad (same on the san) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2009-01-07 : 15:07:52
|
The Particular SPID is 56 that's droping the index. SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID ----- ------------------------------ -------------------------------------- ------------ ----- ------ ---------------- ------- -------- -------------- ---------------------------------------------- ----- --------- 1 BACKGROUND sa . . NULL RESOURCE MONITOR 0 0 01/06 21:00:12 1 0 2 BACKGROUND sa . . NULL LAZY WRITER 82609 0 01/06 21:00:12 2 0 3 SUSPENDED sa . . NULL LOG WRITER 22171 0 01/06 21:00:12 3 0 4 BACKGROUND sa . . NULL LOCK MONITOR 0 0 01/06 21:00:12 4 0 5 BACKGROUND sa . . master SIGNAL HANDLER 0 0 01/06 21:00:12 5 0 6 sleeping sa . . master TASK MANAGER 0 0 01/06 21:00:12 6 0 7 BACKGROUND sa . . master TRACE QUEUE TASK 234 0 01/06 21:00:12 7 0 8 sleeping sa . . NULL UNKNOWN TOKEN 0 0 01/06 21:00:12 8 0 9 BACKGROUND sa . . master BRKR TASK 0 0 01/06 21:00:12 9 0 10 BACKGROUND sa . . master TASK MANAGER 0 0 01/06 21:00:12 10 0 11 SUSPENDED sa . . master CHECKPOINT 210468 9602 01/06 21:00:12 11 0 12 BACKGROUND sa . . master BRKR EVENT HNDLR 15 52 01/06 21:00:12 12 0 13 sleeping sa . . master TASK MANAGER 0 0 01/06 21:00:12 13 0 14 BACKGROUND sa . . master BRKR TASK 0 0 01/06 21:00:12 14 0 15 sleeping sa . . master TASK MANAGER 0 0 01/06 21:00:12 15 0 16 sleeping sa . . master TASK MANAGER 0 1 01/06 21:00:12 16 0 17 sleeping sa . . master TASK MANAGER 0 2 01/06 21:00:12 17 0 18 sleeping sa . . master TASK MANAGER 0 0 01/06 21:00:12 18 0 19 sleeping sa . . master TASK MANAGER 0 4 01/06 21:00:12 19 0 20 sleeping sa . . master TASK MANAGER 0 0 01/06 21:00:12 20 0 21 sleeping sa . . master TASK MANAGER 0 0 01/06 21:00:12 21 0 51 sleeping NT AUTHORITY\SYSTEM NATIMARKSQL4 . msdb AWAITING COMMAND 0 12 01/07 13:00:17 SQLAGENT90 - Id<2912> 51 0 52 sleeping NT AUTHORITY\SYSTEM NATIMARKSQL4 . msdb AWAITING COMMAND 0 0 01/06 21:00:13 SQLAgent - Email Logger 52 0 53 sleeping NT AUTHORITY\SYSTEM NATIMARKSQL4 . msdb AWAITING COMMAND 78 24 01/07 07:30:35 SQLAgent - Generic Refresher 53 0 54 sleeping sa NATIMARKSQL4 . master AWAITING COMMAND 766 241 01/07 12:08:38 Microsoft SQL Server Management Studio 54 0 55 sleeping sa NATIMARKSQL4 . Test AWAITING COMMAND 708234 557167 01/07 13:01:41 Microsoft SQL Server Management Studio 55 0 56 SUSPENDED sa NATIMARKSQL4 . Test DROP INDEX 15 161 01/06 21:29:20 Microsoft SQL Server Management Studio - Query 56 0 56 SUSPENDED NATIMARKSQL4 . Test DROP INDEX 7372140 46921095 01/06 21:29:20 Microsoft SQL Server Management Studio - Query 56 0 57 sleeping DATA\Albert ALBERT . Test AWAITING COMMAND 31 12 01/07 11:50:27 SQL Query Analyzer 57 0 58 RUNNABLE DATA\Albert ALBERT . Test SELECT INTO 141 101 01/07 13:01:37 SQL Query Analyzer 58 0 60 sleeping sa NATIMARKSQL4 . master AWAITING COMMAND 187 53 01/07 12:59:32 Microsoft SQL Server Management Studio 60 0 61 sleeping NT AUTHORITY\SYSTEM NATIMARKSQL4 . msdb AWAITING COMMAND 16 35 01/07 07:15:00 SQLAgent - Job invocation engine 61 0 63 sleeping sa NATIMARKSQL4 . Test AWAITING COMMAND 0 11 01/07 07:14:13 Microsoft SQL Server Management Studio - Query 63 0 (33 row(s) affected) i assume ur talking about the blkby column, everything's empty... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2009-01-07 : 15:18:35
|
joy... kinda mind boggling that it's taking longer to drop an index then it did to add it.this server has 1tb local hdd's and i'm adding the 8tb SAN into the mix, what would you recommend:have the actual data on the local disks, indexes on the san,have the data on the san, indexes local,or a combo of both?any reccommendations on how i can get things to speed up? i though iSCSI was supposed to be close to comprable to direct-attached storage and i'm finding out someone told me a little white lie  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2009-01-07 : 15:22:44
|
| any reccommendations on where i can go (this forum or another) for good pointers?p.s. any good reads for a nub like me to pick up for sql? thanks! |
|
|
|
|
index drop taking 100 hrs :(