What ? - 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)

What ?

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-25 : 10:41:51

I've got a copy of my production database on my laptop server.

Executing a stored procedure on my laptop takes 3 seconds.

The same query , parameters and data on the production server takes 60 seconds.

It's the only database out there on the remote server. The remote server isn't that busy.

If the problem is fragmentation, what maintenance procedures would I run to restore the remote production database into proper form?

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 10:54:06

Well, if you did a dump and restore Sam, I would think the fragmentation would have come along for the ride as well..

What does the sproc do? Does it involve a lot of network traffic?

Did you establish a maint plan for the database?

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-25 : 11:04:11

I used EM Export data to make a copy. I assume that eliminates the fragmentation, maybe not.

The sproc is a few joins, a WHERE and it SELECTs users by department, course completion.

I've made a working copy of the database on the remote server. I can try reindexing or other maintenance activities on the working copy during the day, then test with the problematic query.

Any suggestions on what I might try?

Sam

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-25 : 11:09:03

Have you verified that the execution plans are the same?

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 11:21:57

quote:

I used EM Export data to make a copy

You're confusing me...how? DTS?

And how'd you get the data in to your local? bcp?

And you must have had to worry about RI.

Why didn't you dump and restore...did you only want a couple of tables?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 11:25:39

Also, did you check out:

DBCC DBREINDEX

What's the volume of transactions? Do you have a high level of inserts occuring? If not, then I wouldn't think that's it.

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-25 : 11:28:46

The execution plans are different. This surprised me since the copy on my laptop was made just minutes ago.

On my laptop where the query runs in under 1 second:
Index Seek
Bookmark Lookup
Filter
Sort
Merge/Join Right

On the production server where the query runs in 60 seconds
Index Seek
Bookmark Lookup
Filter
Table Spool/Lazy Spool
Nested Loops Left Outer Join

The execution plan applies to the courserecords part of

FROM Users U
LEFT OUTER JOIN CourseRecords CR ON CR.UserID = U.UserID
AND CR.CourseID = @CourseID
AND CRecordFinish >= @CompletedSince
LEFT OUTER JOIN Branches B on U.BranchID=B.BranchID
LEFT OUTER JOIN Centers C on U.CenterID=C.CenterID

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 11:40:11

How does this plan out on the server:



   SELECT * 
     FROM Users U 
LEFT JOIN (SELECT * FROM CourseRecords
	    WHERE CourseID = @CourseID 
	      AND CRecordFinish >= @CompletedSince) AS CR 
       ON CR.UserID = U.UserID 
LEFT JOIN Branches B 
       ON U.BranchID=B.BranchID 
LEFT JOIN Centers C 
       ON U.CenterID=C.CenterID

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-25 : 11:44:20

Certainly the table spool is the proximate culprit; the ultimate one might be a lack of statistics.

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 11:47:59

Table spool/Lazy spool look worse than a table scan...not sure though..it's certainly doing more work than a scan..

It takes ALL of the data and makes a copy in tempdb, so it can reuse if needed (unless if it need to rebind the data, then it throws it away and does it again)

Yuck

I go with updating statistics...

Also what are the indexes on all of the tables?

And how many rows in each?

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-25 : 11:49:08

I found exactly the same execution plans and speeds with the revision.

I also copied the database to a 3rd server (using EM), and to the production server. This query only runs poorly on the production server.

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-25 : 11:51:38

How do I update the statistics?

Everything is indexed.

Sam

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-25 : 11:59:54

Use CREATE STATISTICS/UPDATE STATISTICS.

Not sure what 'everything is indexed' means. I take it you have verified that all the objects are the same in both databases - same query, same tables, indexes, sql server versions, etc?

Jonathan
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-25 : 12:00:35

Wait..

Why would the execution plans differ based on statistics. I'm running these tests on copies of the database made on 3 different servers. The copy on the originial / production server is the poor performer.

Wouldn't this suggest something other than statistics?

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-25 : 12:04:11

The db copies were all made this AM for the purpose of testing, so the statistics should be identical in all 3 dbs.

There's a slight difference in the SQL server revision

The query runs well on this server
SQL Version is 2000 - 8.00.760

And performs poorly on this server
SQL Version is 2000 - 8.00.679

But I can't imagine the difference being so great because of this.

When I said everything is indexed - I meant the columns used in the Join criteria.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 12:04:56

Check the error logs to see if you have aproblem and/or run dbcc to if it reports anything too.

What about the number of inserts occur on your prod db..is it a lot?

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-25 : 12:10:58

There's no traffic / inserts on the test databases. And none of the servers appear to be heavily used.

I haven't checked the error logs, but remember, the 60 second query returns the same results as the 1 second query, so it's not likely an error.

I looked at the server log, there's nothing special there. If there's an error log, I'm not sure where to find it.

Sam

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-25 : 14:16:02

quote:
Wouldn't this suggest something other than statistics?

That's my point - your challenge to this board has been "find the difference". Same query with same data, but different machine. We know we can factor out the hardware, so what's different? SQL Server version? indexes? parallelism? statistics?

Your up-level SQL Server performs better than your down-level; this suggests a service pack fix that addressed, in part, the optimizer decisioning. Perhaps it's a combination of factors.

You might try a different method of transferring the objects. I don't use the EM export wizard myself. Try 'Copy DB Objects' in DTS, a database detach/reattach, or perhaps a backup and restore.

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 14:49:53

If there's not a lot of activity on the prod box, then statistics would be fine, because it doesn't change much..

Statistics tell the optimizer the best path to the data...the depends on the distirbution or cardinality of the (how many of each thing)

The indexes should be fine as well, since they get hosed whene theres a lot of inserts, which you don't have.

That leaves the Service pack issue as Jonathan has mentioned, or network traffic. I mention Network traffic, because if you're trying drag tons of info across, it might be a factor. Bu I don't it because you say it's onle 1-3 seconds on the laptop.

First, I'm curious that there is no maint plan for a production box.

Depending on the size of your db will disctate what you do.

One of mine is a 2 gb db.

Maint Plan 1 Does a Full dump every night
Maint Plan 2 Dumps trans logs every 15 minutes
Maint Plan 3 Does Optimization and Data Integrity checking Every Sunday at 4:00am

Second, Try the dump and restore to your laptop. Dump through EM..it's simple (I really should invest the time to write a script...it is the laziest way to go)

Restore using this:


Create Proc usp_Restore_Production as


ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE Database
   FROM DISK = 'D:\Tax\BackUp\Database.dmp'
   WITH   MOVE 'Database_Data' TO 'D:\Database\serverinstance\MSSQL$serverinstance\Data\Database.MDF' 
        , MOVE 'Database_Log'  TO 'D:\Database\serverinstance\MSSQL$serverinstance\Data\Database_Log.LDF'
	, REPLACE

And see if it is something about your db...

I say that because you can not gaurentee it is, and needs to be eliminated.

You restore that on your laptop, and no issues, then we go somewhere else...BUT, you restore it, and BOOM 60 seconds...

You've got other problems.

This game is all about be 100% sure about things.

There are no Miracles...

ALTER DATABASE TaxReconDB_Prod SET READ_WRITE

GO
[/code]

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-25 : 14:50:54

Since you are trying to copy production, I would not suggest detach/attach since the production database would be down while the detach and attach occurs. I suggest using BACKUP/RESTORE method. I have only used the wizard once, just to see what it did and how fast it was, and will never use it again because it is so very slow. BACKUP/RESTORE method will bring over the statistics, indexes, etc..., everything will come over. Then the only difference would be the server itself (service packs, hardware, software, etc...), but at least it rules out the database.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 15:07:51

quote:

Since you are trying to copy production, I would not suggest detach/attach since the production database would be down while the detach and attach occurs. I suggest using BACKUP/RESTORE method. I have only used the wizard once, just to see what it did and how fast it was, and will never use it again because it is so very slow. BACKUP/RESTORE method will bring over the statistics, indexes, etc..., everything will come over. Then the only difference would be the server itself (service packs, hardware, software, etc...), but at least it rules out the database.

Tara

SCARY

Brett

8-)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources