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.
| Author |
Topic |
|
jmiller121
Starting Member
8 Posts |
 Posted - 2009-09-09 : 10:25:10
|
| Using SSMSE on SQL 2005 Standard Edition (64-Bit) box...Database has 500+ tables (12+ GB in size)PRELUDE TO PROBLEM:One particular table is a "CITY" table that has City, State, Zip, Municipality, County, etc. for "city lookups". The table has about 1,500 records. The table has 5 indexes: City, Zip, Municipality, City/State/Zip, and PK (an Indentiy column) Last weekend I did a BULK INSERT to add about 4,500 records to the table. After I did that, the program that accesses that table ran extremely slow. I'm assuming that the indexing wasn't properly updated which caused the program that was accessing the table to "slow down". So I'm trying to get some information about the indexes on that table.DESCRIPTION OF PROBLEM:Using SSMSE, I go to the CITY table, expand INDEXES, and open the Properties of one of the indexes. When I choose the FRAGMENTATION page, it takes about 2-3 MINUTES for this info page to come up. If I try to Rebuild the index, it also takes about 2-3 minutes to display the confirmation box to rebuild the index. If I "Rebuild All Indexes", it can take about 10 minutes to display the confirmation page, and then when I press OK, I never get a confirmation that it was rebuilt.QUESTION:My question is, What the hell would cause this slowness on a table with only 1,500 records? (I am using a restored backup copy of the database, pre "BULK INSERT") Is this normal? It doesn't appear to be because when I choose the same properties on the NorthWind db on the same box, everything pops up right away.Thanks for any input!Jason |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-09-09 : 16:33:26
|
| Check the table with DBCC CHECKTABLE |
 |
|
|
jmiller121
Starting Member
8 Posts |
Posted - 2009-09-10 : 15:26:25
|
| Did that... didn't appear to have any errors....DBCC results for 'City'.There are 1580 rows in 26 pages for object "City".DBCC execution completed. If DBCC printed error messages, contact your system administrator.??? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-09-10 : 18:41:57
|
| Yes I agree GUI is slow with fragmentation.Use DMV for Fragmentation and ALTER INDEX..... REBUILD to rebuild it. |
|
|
|
|
|
|
|
|
SSMSE slow when trying to get Fragmentation info