January 29, 2004 at 11:57 am
Kind of long...We are having some problems at one of our customers and we are trying to figure out what is going on. The scenario:
Server 1 (Development) - 2 x CPU, 1GB RAM - Many databases with low usage
Server 2 (Production) - 4 x CPU, 4GB RAM - Many databases with high usage
Problem 1 - We have a table (tUsers) that has about 700,000 records in it. We run our application against the development machine, its fine. We moved the db to the production server and we get SELECT times running about 45-70 seconds. We've determined that this is due to the production table being dropped from memory (it's a shared server) and having to be reloaded. We dug a little deeper and found that the development table is ~150 MB with ~50MB of index. The production table, which at this point had about 760,000 records, is approximately ~800 MB with ~350 MB of index. Same table, no schema changes, nothing. The table has 26 fields; the largest is a VARCHAR(35). No BLOBs. The production dba did a shrink on the database and got the production table down to ~350MB with ~ 250 MB of indexes. Why is this so much larger? It doesn't make sense that 700,000 rows on one server = 150MB and 760,000 rows on another server = 350MB? What else can we look at?
Problem 2 - We have another table (tUserAddress) that has about 172,000 records. On the production server, a SELECT statement takes about 8 seconds to run. The SELECT statement ends up doing a table scan, thus the slow speed. We made an exact duplicate of the table (tUserAddress_Test) and run the same SELECT statement. It runs in 4 seconds. Below are the results of the Table Scan and SP_SPACEUSED for each:
tUserAddress Table Scan
RowCount: 2,943,099
Estimated Row Size: 125
I/O Cost: 1.96
CPU Cost: 0.189
Number of Executes: 20
Cost: 2.149212 (93%)
Subtree Cost: 2.15
Estimated row count: 172,121
tUserAddress_Test Table Scan
RowCount: 2,971,167
Estimated Row Size: 47
I/O Cost: 0.945
CPU Cost: 0.0945
Number of Executes: 20
Cost: 2.079553 (93%)
Subtree Cost: 2.08
Estimated row count: 172,094
tUserAddress SP_SPACEUSED
Name: tUserAddress
Rows: 172,138
Reserved: 20,880 KB
Data: 20,768 KB
Index_Size: 16 KB
Unused: 96 KB
tUserAddress_Test SP_SPACEUSED
Name: tUserAddress_Test
Rows: 172,094
Reserved: 44,056 KB
Data: 20,016 KB
Index_Size: 104 KB
Unused: 23,936 KB
I've highlighted the apparent differences. What should I look for next? What can be causing this?
Thanks.
Matt
January 29, 2004 at 12:41 pm
"The production dba did a shrink on the database and got the production table down to ~350MB with ~ 250 MB of indexes. Why is this so much larger? It doesn't make sense that 700,000 rows on one server = 150MB and 760,000 rows on another server = 350MB? What else can we look at?"
Could be table fragmentation due to the insert/update/delete and fill factor difference between DEV and Prod for same table.
How did your shrink the database? Vefrify the fill factor setting in DEV table and rebuild the indexes with same fill factor on table in Prod.
"On the production server, a SELECT statement takes about 8 seconds to run. The SELECT statement ends up doing a table scan, thus the slow speed. We made an exact duplicate of the table (tUserAddress_Test) and run the same SELECT statement. It runs in 4 seconds."
It looks like same query generating different execution plan, Do both tables have same indexes?
"Server 1 (Development) - 2 x CPU, 1GB RAM - Many databases with low usage
Server 2 (Production) - 4 x CPU, 4GB RAM - Many databases with high usage"
What about query parallel execution setting on both servers? The query runs on production could run parallel which performs slowly sometime. Can you also post the query execution plan here from both systems?
January 30, 2004 at 2:02 pm
From what you have posted it is apparent that the automatically grow file is not set the same way in development and production. Never use the default 10 percent, for when the db size increases more space than is reuired is allocated for each growth. stick in some fixed size to grow by say 50 mb for example.the response during the expansion time also would be slow. Are you running nightly jobs that insert and deletes a lot of data? even that could cause such problems of automatically expanding the dbs .
January 31, 2004 at 3:17 am
Check if you have the same indexes.
Special attention should be made on the clustered index.
Reindex all of the indexes of your table with dbcc dbreindex.
This should reduce the size dramatically especially if it was not made for long time and a lot of insert/update/delete statemets has been issued
Bye
Gabor
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply