Slow Running MS SQL 2000 Server (Round 2)

Hi
I am trying to fix a slow running Stored Procedure the procedure runs 7 to 8 times slower on my Database at discountASP than on my local Development Environment and appears to be getting worse as time goes by.

I have run a "DBCC SHOWCONTIG" on my database at discountASP with the results shown below.

With my limited experience (Books, School and this site) it would appear the system tables need to be defraged. According to my book this is done by Dropping and then Recreating the index.

Question:
1) Would you agree that ALL "sys...." tables EXCEPT for 'sysfiles1', 'syspermissions', 'sysusers' and 'sysfilegroups'tables need to be defraged ?
2) Is it as simple as running DROP INDEX and CREATE INDEX in Query Analyzer while connected to my DB at discountASP ?
3) Do you think doing this will fix my problem ?

Please help I've been fighting with this problem for a month now.
Thanks
albanello

DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 521
TABLE level scan performed.
- Pages Scanned................................: 17
- Extents Scanned..............................: 8
- Extent Switches..............................: 13
- Avg. Pages per Extent........................: 2.1
- Scan Density [Best Count:Actual Count].......: 21.43% [3:14]
- Logical Scan Fragmentation ..................: 52.94%
- Extent Scan Fragmentation ...................: 75.00%
- Avg. Bytes Free per Page.....................: 2713.8
- Avg. Page Density (full).....................: 66.47%
DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 521
TABLE level scan performed.
- Pages Scanned................................: 8
- Extents Scanned..............................: 6
- Extent Switches..............................: 6
- Avg. Pages per Extent........................: 1.3
- Scan Density [Best Count:Actual Count].......: 14.29% [1:7]
- Logical Scan Fragmentation ..................: 50.00%
- Extent Scan Fragmentation ...................: 83.33%
- Avg. Bytes Free per Page.....................: 4087.3
- Avg. Page Density (full).....................: 49.50%
DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 521
TABLE level scan performed.
- Pages Scanned................................: 30
- Extents Scanned..............................: 9
- Extent Switches..............................: 21
- Avg. Pages per Extent........................: 3.3
- Scan Density [Best Count:Actual Count].......: 18.18% [4:22]
- Logical Scan Fragmentation ..................: 43.33%
- Extent Scan Fragmentation ...................: 88.89%
- Avg. Bytes Free per Page.....................: 4704.3
- Avg. Page Density (full).....................: 41.88%
DBCC SHOWCONTIG scanning 'syscomments' table...
Table: 'syscomments' (6); index ID: 1, database ID: 521
TABLE level scan performed.
- Pages Scanned................................: 69
- Extents Scanned..............................: 13
- Extent Switches..............................: 43
- Avg. Pages per Extent........................: 5.3
- Scan Density [Best Count:Actual Count].......: 20.45% [9:44]
- Logical Scan Fragmentation ..................: 42.03%
- Extent Scan Fragmentation ...................: 69.23%
- Avg. Bytes Free per Page.....................: 3959.4
- Avg. Page Density (full).....................: 51.08%
DBCC SHOWCONTIG scanning 'sysfiles1' table...
Table: 'sysfiles1' (8); index ID: 0, database ID: 521
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 6508.0
- Avg. Page Density (full).....................: 19.59%
DBCC SHOWCONTIG scanning 'syspermissions' table...
Table: 'syspermissions' (9); index ID: 1, database ID: 521
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7700.0
- Avg. Page Density (full).....................: 4.87%
DBCC SHOWCONTIG scanning 'sysusers' table...
Table: 'sysusers' (10); index ID: 1, database ID: 521
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7280.0
- Avg. Page Density (full).....................: 10.06%
DBCC SHOWCONTIG scanning 'sysdepends' table...
Table: 'sysdepends' (12); index ID: 1, database ID: 521
TABLE level scan performed.
- Pages Scanned................................: 3
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 5322.7
- Avg. Page Density (full).....................: 34.24%
DBCC SHOWCONTIG scanning 'sysfilegroups' table...
Table: 'sysfilegroups' (96); index ID: 1, database ID: 521
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 8058.0
- Avg. Page Density (full).....................: 0.44%

 

Hi


<Junior>In reality, system tables are so small ......


I don't understand I thought fragmentation is what causes problem if a index needs to keep jumping around to get to the requested data that's what causes inefficiency.


<Junior>There is a way to reindex them, but running the ..........


This is why I am so hesitant to try something out before talking to somebody that has more experience. I don't want to make things worse !


Let me restate the problem:
1) ALL my Stored Procedures run at least 7 times slower at discountASP than on my local computer. It does not matter if the stored procedure takes 100m seconds or 1 minute to execute locally, at discountASP it would take 700m Second or 7 Minutes to execute the same stored procedure.
2) It seem to be getting worse, I've been with discountASP for a Year now this problem did not show up till around a month ago.


Causes:
1) discountASP Server running Slow (Tech Support says there is nothing wrong with there server)
2) My database has something wrong with it (That's what I am trying to figure out)
3) It's NORMAL for discoutASP to execute slower due to other user databases on the same server as me. (No one I have talked to ever has suggested this as a possibility)


Solutions:
1) Determine problem with my database (HHHHHHHHeeeeeeeeellllllllpppppppppp)


Questions:
1) Is Causes number 3 possible ??????
2) Can you think of other causes ?
3) What can I look at and/ or do to isolate and fix ?


Thank You for your response
albanello

 

It could be that the possible suggestions to help with your problem have been exhausted, and that's why no one is answering. A lot of suggestions were made, I don't know how many you tried...

mjp
DiscountASP.NET
<SUB><SUP>http://DiscountASP.NET

 

HelloHelloHello is anybody out theretherethere

Was it something I said ?

albanello

 

Hi mjp


thanks for your responce.


I tried all the suggestions, most of them had to do with optimizing the stored procedure, which I have done, with a enormous improvement and I am greatfull for that. But the root problem is STILL that it takes 7 times longer to run the improved SP at discountASP than on my local computer (Side note ALL SP run slower). This is the problem I am trying to answer now. You can read a previous post which I will restate here:


*******


Causes:
1) discountASP Server running Slow (Tech Support says there is nothing wrong with there server)
2) My database has something wrong with it (That's what I am trying to figure out)
3) It's NORMAL for discoutASP to execute slower due to other user databases on the same server as me. (No one I have talked to ever has suggested this as a possibility)


Solutions:
1) Determine problem with my database (HHHHHHHHeeeeeeeeellllllllpppppppppp)


Questions:
1) Is "Causes" number 3 possible ??????
2) Can you think of other causes ?
3) What can I look at and/ or do to isolate and fix ?


********


I needstuff I can check relative to the database that could cause this problem (7 times long at discountASP than locally) maybe the answer is cause 3 but I don't know. I don't know how else to ask the question. Right now I am trying to figure out if there is some sort on maintenance I should be performing on the DB that I'm not aware of. What I need is a DataBase Administrator. Remember my experiance in ASP.NET/SQL is limited to School, Books and my experiance with this site. My experiance with Database Adminiatration is 0. As for as I knew DASP took care of making sureDB was not fragmented. I just found out resently they don't but people I have talked to say the DB is not fragmented enough to cause this problem.


Sorry the suggestions made did not fix the root problem. As I'm sure all of you know sometime the answer is not simple but it always seems to me that the most difficult problem end up being theobvious solution.


Thanks again


albanello

 

Hi Junior


I have been with discountASP for a year now. This 1SP has always had a long execution time but never timed out (Script, Command and Connection Timeout set at 4 Minutes). About a Month agowe started getting timeouts. At that time I noticedthe SPtaking longer to execute. Someone else has been doing the updates for the last year and I believe the time has gradually increase over the year so it was not noticed. But I noticed the increased time since I have not been doing it everyweek.The execution time for this 1 SP WAS 3.5 Minutes (but all SP seem to take longer) I have improved it (with help fromthe forumto 1 Minute). I'm trying to even cut that down somemore. I have set Script, Command and Connection timeouts to 7 Minutes and this 1 SP still times out.


I beleive (Speculation)there may be 2 problems:


1) Gradual increase in time.


2)Something happening a month ago to cause the execution time to jump causing timeouts.


Problem 1 could be mydatabase grew, fragmented, or/and ?????.....


Problem 2 could be something changed at discountASP, something I did a month ago or/and ????....


The Last thing I did when this problem was first noticed was I activated a Scheduled Task that read a RSS feed, that automated the Main table update, once per day. The SP is NOT run in this Scheduled task, the SP is still a manual task.The only thing I know of that discountASP did, that was around this time (based on a E-Mail from discountASP) was you changes the E-Mail (system or something). I am not saying either of these thing caused the problem I'm just trying to relate the problem to some event.


Presently I am still trying to optimise and rework the SP's, but I would like to find out what I need to do to maintain my DB, to see if that is what is causing the problem.Remember DBA experience=this site only.


Thanks for your responce.


albanello

 

Hi Bruce


Please see post to Junior


To answer your specific questions all SP run slower. Moving code has been suggested by the forum and I will do that if I have to BUT as I stated before that would be a major rework that I would like to save as a last resort. Moving to ASP.netmay shorten the time but it would still be longer than local execution. Right ? I'm still working on thing to improve the application, but I would really like to solve the ROOT cause of the problem (Execution time longer at discountASP than local)


Thanks for your responce.


albanello

 

Bruce Thank You Thank You Thank You !!!!!!!!!!!!!

That was it I just ran the long SP at discountASP and it took 35 Second. That's more like what I would expect, discountASP should be faster than local execution.

Now I have some questions:

It seems that SQL 2005 .......
My DB at discountASP is MS SQL 2000 is this just a typo on your part ?

We restarted the process.....
Is this something I can do ? I have used the restart/purge in control panel but did not make a difference at the time.I also did a "sp_recompile' at one point did not notice a change, this SP is suppose to force a recompile per a "O'REILLY" book I have.

As for why the execution plan was cached, there are many possible reasons.......
I will look into all of these and correct if applicable. I know I saw the "organizing the procedure with DDL at top of the page ....." in a book or online and tried it but it did notmake a difference at the time I will correct this in my SP now.

.....creating a table inside the stored procedure rather than truncating it.....
What do you mean when you say "truncating it" ?

....not breaking down your procedures...
Do you mean breaking down a large procedure into a bunch of smaller procedures ?

...Auto-parameterization...
Is this a common term I will be able to search for ? If not what do you mean by it ?

.....of these things and more can cause the server ....
This is pretty bad because I'm sure I'm not the only one that could make any of these mistakes, I hope the real solution, at discountASP, isto find out why the server allowed this to happen. It should recover automatically shouldn't it ? Otherwise there would be thousands of sites with this problem. (I can not be the only one !)

It might be worth your while to have a consultant ........
I wish I had the money to have a consultant look it over. Thats why I'm at the forum. I am the only consultant, programmer, DBA I can afford. I have been in contact with one of my ex teachers and he has offered to help. I'll show him your post and see if he can help mecorrect any problems on my end that may have caused the problem.

Look forward to your responce
Thank again
albanello

 

I can answer a couple of these, as I spoke to the DBA about this as well.

When he talked about creating the table he was wondering why you create the table each time rather than keeping the table in the databasepermanently and simply updating the data within the table each time. "Truncate" just means todelete the contents ofthe table.

Your procedure did not effect the performance of the entire server, just your database, since the server was using the cached version.


mjp
DiscountASP.NET
<SUB><SUP>http://DiscountASP.NET

 

Hi mjp

All the "Results" tables (10 tables for each "Numbers" table) are created as a function of the data in the "Numbers" table. The "Numbers" table generally just has two rowsinserted a week, at the end of the table. If this was all that happened I could do what you suggest, BUT, it is possible that a Update or Delete could be performed at any row in the "Numbers" table. It is much easier to drop all the "Results" tablesand recreate them.

I was not saying my procedure effected the performance of the entire server, what I was saying was whatever I did to cause My DB to get screwed up I'm sure, of the 100 of thousands of developer on the internet somebody else hasmade the same mistake. I would think microsoft or discountASP would have a solution to detect the problem and "restart the process" automatically.

Bruce's list of thing that could have cause the problem is pretty long and without end. I will fix the things he listed BUT can you tell me is there something I can do to "restartthe process" or do I have to go through this again if it happens again. Does the "sp_recompile" SP do the same thing ("restart the process")? or is the control panel restart what I need to do?

Thanks for your reply
albanello

 

Hi mjp

I look forward toreadingyour DBA's post. Thanks

albanello

 

Dropping and recreating the tables may be easier for you from a programming standpoint, but it is definitelymore resource intensivefor the db server to drop/recreate/populate than to simply update. If the goal is to increase the efficiency of your application, that is something to consider.



albanello said...


...is there something I can do to "restartthe process" or do I have to go through this again if it happens again.



I don't believe that youcan dumpa cached execution plan yourself.Our DBA mentioned to me on the way out of the office that he may comment further on this threadif he has time later tonight.










mjp
DiscountASP.NET
<SUB><SUP>http://DiscountASP.NET

 

Thank for your responce junior


I'm in the process of reworking ALL of my SP's and will implement the items you have noted.


1) Break Down stored procedures


2) Truncate instead of Drop


3) Execute DDL first then DML


4) I don't think I had any of my SP names with a sp_..... prefix the only sp_ is sp_executesql and that is a server procedure. I will look at my code and make sure I don't have anything named with the sp_ prefix.


Thanks again for your help


albanello


Ps: I want to thank everyone for there help junior, bruce, mjp, Loel Thoms, wismx, Gobannosand Jamie Thanks

 

Hi Junior

I'm working on it now. I've got like 20 Stored Procedures to rework, but the one I used as an example is done and I'll try and post it next week.

albanello

 

Hi junior

Ok here is the reworked Stored Procedure. If you or anybody in the forum have any observations that may improve its performance please don't hesitate to let me know, the SP is not written in concrete.

1) I have moved the DDL instructions to there own SP. The only problem I see here is that the First table TRUNCATE, which is a DML instruction may be run before the Second table DDL. I could not come up with a better way of dealing with this. If someone has a better idea please let me know.

2) I converted all EXEC (SQL statements) to EXEC sp_executesql's they are suppose to NOT recompile every execution.

3) I optimized the WHILE loop to minimize instruction execution by moving instructions outside the WHILE loop that did not require execution every pass of the WHILE loop.

4) The "MMWhiteBallSkipHitTbl" I create hard code as opposed to dynamically since its number of column is known. I also only create the columns needed (I was creating more than needed so I could change the Application code if I wanted to display more columns).

5) I added "dbo." to all Table calls, this is suppose to help the SQL Server so it can go directly to the correct DB table as opposed to looking in system directories first.

6) I add "SET NOCOUNT ON/OFF" this is suppose to speed up execution.

7) I declare fewer variables and reuse the ones I have to minimize memory usage.

What I have displayed, at the bottom of this page, is a SQL batch that will create all required SP needed to construct the Since Hit and Skip Hit tables. At the end of the Batch is:

DBCC PROCCACHE
EXEC dbo.MMUpdateWhiteBallSinceHit_SkipHitTbl 1, 56
DBCC PROCCACHE

This was used for testing (locally) to verify the Stored Procedure Cache did not grow every time the "MMUpdateWhiteBallSinceHit_SkipHitTbl" SP is executed. Prior to the SP revision the Cache grew I'm still unclear as to what the "PROCCACHE" number represent (bytes, Mega bytes or apples) but before the SP revision the numbers grew with every execution of the SP, after SP revision they DO NOT increase at all, other than the first time I run the SP (Locally). Local computer turned off so first execution needs to recompile SP.

Please let me know if you see any further improvements, I hope I implemented correctly your suggestions.

Thanks
albanello

 

Hi everybody


Well its been 4 Monthsand the table update stored procedures have been working with no problems. TILL LAST WEEK 03/07/07. Now they are taking a long time to execute again. Example one of the table stored procedures (the biggest)was 36 seconds now it times out after 12 Minutes.


Very disappointed


albanello

 

Hi onestepxcom


I don't know if you have read the entire post "Slow Running MS SOL2000 Server (Round 2)" but don't miss reading"Slow Running MS SQL Server" original post I had this problem a year ago it took me 6 mo months of posts to try and get it fixed (figure out what the problem was). I'm pretty sure itwas something I am doing in my stored procedures and a problem with MS SQL Server not clearing the cache, but I thoughtit wasfixedtill about 3 weeks ago when the stored procedure started running (approx 7 times) slower. Five months ago discountasp.net restarted there server which fixed the problem (Cleared the cache)and I reworked ALL of my stored procedure. I think the problem is the Procedure Cache gets filled up and "lazy writer" is not purging the procedure cache as it should. When discountsap.net restarted there server that cleared the cache, and everything worked fine. Over time the cache fills up again and the problem shows up again. I have stopped running my stored procedure hoping the cache clears its self out and am presently in the process of reworking my stored procedures again. I'm not sure what causes the problem but I posted one of my reworked stored procedure (5 months ago), and did not get any responces as to there still being a problem with the Stored Procedure. All this is speculation and a semi-educated guess on my part.


My tables are SMALL the largest table has around 1000 rows of smallint data. My development enviorment works fine also, I beleive this is because my conputer getes turned off ever night so the procedure cache getes cleared every time it restarts. Try running locally in your query analyzer "DBCC FREEPROCCACHE", execute yourqueries and then execute "SELECT * FROM master..syscacheobjects" locally. this will show you how much stuff is being put in your Procedure Cache, then rework your queries to try and minimize the stuff put in the cache. Again I am no expert by any means but this is what I am trying to do to fix the problem.


Good luck ! If you figure it out please post the solution.


Thanks


albanello

 

I have similar problems.
But instead i have dynamic queries. I join usualy 3-4 tables. Yes tables are quite big 100-200k records. But on dev enviroment everything works. And quickly. I made hundreds optimizations. But on AspDiscount i receiving timeounts. 1-5% of queries. And queries are much slower. After couple complains, AspDiscount worked perfectly. But from last week problem arise again - since 30/04/07. No change to database. No change to code. But I receive timeouts in my exception log. And slow response time.
Everything on SQL 2005.