Slow MSSQL Server ?

Hi

I have some stored procedures that appear to be running slower than they use to. Does anybody know if the SQL Server has been slowed down ? Can anybody give me any ideas as to how to locate the problem ? They are the same stored procedures I have been running for months.

albanello

 

Maybe you need to index your data?



Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

I have a few DBs running on the dasp platform. I haven't noticed any performace difference in any of them. I haven't heard any reports from techsupport about db slowing either.


These problems are usually difficult to debug. They could be anything from code, to the stored procedures, to the tables, indexing, server, etc.



Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

Both the SQL Server 2000 and 2005 DB servers you guys setup for me always ran great.

This sort of remote connectivity problem is becoming impossible to diagnose properly, so many underlying factors.

 

Is the slowness between your site and the sql server or between your connection and the sql server?


Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

HI Joel


Thanks for your reply.


I believe I already am indexing my data, but I'll have to check on that. The point is it seams to execute slower than it has for the last year. My time point of reference is relative to previous experiance.


Since you are a Forum Moderator does that mean you have inside knowledge of the workings of the MSSQL server. Has it been slowed down for some reason.


The only thing that has changed is that I activated a Scheduled Task and have allocated some application Public Shared memory. The Scheduled Task would not have affected the SQL server,right ? And the allocated application Public Shared memory is seperate from the SQL stored procedure memory isn't it ?


Frank A

 

Hi everybody


Thank You for your responses. I have never had so many at once.


To start out with my experience, with WEB Applications,is limited to school and this site so your help is greatly appreciated. I am a programmer by trade (20 Years) so I'm not a complete newbe. In a previous post I stated "The only thing that has changed is that I activated a Scheduled Task and have allocated some application Public Shared memory" since nobody mentioned this does that mean this could not have caused the problem. The scheduled Task is run once per day.


Bruce:


Can you direct me to something that will tell me how to defragment and reindex. Is there a tool in SQL 2000 or DiscountASP.net that does this. I will do some searching.


wisemx:


I agree up till now (About a Year) I have had no problem with the speed of the application server or the SQL server and if something seemed slow I would just rework my code and it would be better. The only reason I am asking now is because it was working just fine up to about a week ago and now it's slow. I can't see any changes I've made to the application that would cause this and I haven't made any changes to the SQL DB in the last month.


Joel Thoms:


How do I make the determination if the "slowness between your site and the sql server or between your connection and the sql server". The Site andSQL DBare both at discountASP.net.


Thanks again


albanello

 

junior


Thanks for your responce.


WHaaaaaat ? "You can find further info in SQL Server BOL" What is BOL ? I'm hoping it explains "DBCC SHOWCONTIG " and "DBCC DBREINDEX "


Frank A (Experiance with WEB Application/SQL DB limited See previous post)

 

I think junior was referring to Books OnLine:

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx


http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en

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

 

junior

I ran DBCC SHOWCONFIG Customer with the results shown below:

DBCC SHOWCONTIG scanning 'Customer' table...
Table: 'Customer' (1102327387); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 5
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 1.3
- Scan Density [Best Count:Actual Count].......: 25.00% [1:4]
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 1772.0
- Avg. Page Density (full).....................: 78.11%

With my understanding of the documentation since index ID: 0 that means there are no index. All tables in my database have index ID: 0.

I ran DBCC REINDEX anyway then ran DBCC SHOWCONFIG again with NO change to the output.

I still think the discountASP server is running slower. I ran one of the stored procedures on my local computer (in query analyser) and it took 3 Minutes 30 second. I ran the same stored procedure on my site at discountASP and it took 4 Minutes 30 seconds. It use to run faster the reason I became aware of this is because this stored procedure times out. I have the connection, command and script timeout set at 5 Minutes. I don't know what to try next. SHOULD I MAKE MY TIMEOUT 6, 7........10.....minutes.

All help is appreciated !

albanello

 

Wow... 3+ minutes is far far too long for a single query to be running. Indexing is a bit of an art, even I admit I could be better at it.

The difference between 3m 30sand 4m 30sis only a 25% increase in time. You should be able to get this to run in seconds, not minutes.

This sounds like an indexing issue or a really bad query.


Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

Again thanks for your responces.

Bruce:
I don't think I have any Indexes set up

Joel Thomas:
Who said it was a simple query. But as I said "I have limited experiance" so it may be a "really bad query".

Junior:
I bow to your superior knowledge but I just don't see Indexing will Help. I will/am looking into it further. Could you look at the design and tell me if you still think indexing will help.

DESIGN:
Basicly it's a very simple Database as compared to what your probably use to. I only use about 2% of the DB DiscountASP.net allocates for there MS SQL 2000. It's over kill but it a learning experiance for me.

The database consists of 10 flat tables. There is a Main table of 990 rows and 14 columns.Two rows areinserted each week. Generaly there are no deletes and very very very few updates/changes. The other 9 table are DROPPED and reCREATED, from the Main table, every week. The section of the stored procedure that is taking the time does not even use the Main table but it is, what I would call, extremly number crunching intense. It is 990 rows by 25 columns. Basically there is 1 read and 1 write (integer) to each cell (990*25*2) 49500 read/writes SELECTS/UPDATE. This number crunching is done in a while loop. I have to refresh my understanding of indxing to see how to implement it in this number crunching table.

After the 9 tables are created they are ONLY used to display inDataGrids.

I can see where the main table could be indexed. But as I said it is not used in the section of the stored procedure that is taking the 3 minutes to execute.

I look forward to you responce.

Thanks again

albanello

 

Ya... I'd be interested to see what kind of number crunching you are doing... I'd like to see that WHILE loop.


Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

No I obviously don't have a full understanding of what you need.

The main advantage of DB results with the .NET Framework is the 2-way connection.

I don't want to make this seem like it will be easy but it may indeed be more robust to break some procedures out of the SQL side into server code.

As you look at your project can you imagine how that could be done?
Not the actual code, just the flow...

 

wisemx

When you say "break some procedures out of the SQL side into server code" what do you mean by server code ? Is that the WEB Application code (ASP.NET)?

It was my understanding that the best performance would be to run a Stored Procedure in the SQL Server.

I'm open to all suggestions I just need to understand what you saying.

albanello

 

Yes indeed, I was referring to server-side ASP.NET code with a groovy 2-way DB connection.

I used to get that from clients all the time in ASP contract development.
Everyone wanted live DB connections...Click here and see the resulting data.
Boy was that a lot of code to workout.

Now with the .NET Framework your ASP.NET server-side code is an alive 2-way connection.

 

Wow

Do you really need to do all that casting, converting, looping, qualifying against the data?
Can't you for example create a solution that works in code with the initial data values?

 

Thanks for both your responces

wisemx:
You say "Click here and see the resulting data" I don't see anything did you forget to put the link in ? I would really like to see the resulting data.

Bruce:
I agree it would be easier todo thisin the application, as I said I thought the SQL was suppose to be the better performer. I'll look into it. Would indexing still come into play ?

Again just to be sure I'm understanding you both. Your saying I should construct the Data tables in the ASP.net code amd NOT with a SQL Stored Procedure?

Thanks again

albanello

 

Sorry bro, by "Click here and see the resulting data" I meant that's what the clients were always asking for.
Would take at least 15 minutes to explain to them about disconnected data in ASP.

I can indeed see how my post was confusing...

 

OK ! Well you asked for.

The stuff bracketed with "QAQAQAQA" is only in for the debugging of the query. This is really a stored procedure. The SP stuff is commented out so I can run it in the query analyzer.

The "INSERT MyTempTbl (RunDateTime) VALUES ( GETDATE())" are put in as time stamps so I could see what section of the query was taking so much time.
This is the result of the "MyTempTbl" time stamps.
02006-10-04 18:06:51.200
12006-10-04 18:06:51.750
22006-10-04 18:07:13.577
32006-10-04 18:07:13.903
42006-10-04 18:10:24.187
You can see that all the time is taken up by the last While Loop at the bottom of the page.I have marked it for you with "LastWhileLoopLastWhileLoopLastWhileLoopLastWhileLoop"

I'm not going to explain what is going on because it would be a book, but if you have questions I would be more than happy to answer.

The SELECT and UPDATE statements are created dynamically so I can write/read different location in the PBWhiteBallSkipHitTbl and PBWhiteBallSinceHitTbl.

The Number cruching is NOT any fancy math it's just reading every location in the PBWhiteBallSinceHitTbl adding 1 and writing the new value back to the PBWhiteBallSinceHitTbl or the PBWhiteBallSkipHitTbl. Nothing fancy but just time consuming. (990 rows 25 colums Read/Write) 990*25*2 = 49500 read/write

Thank for your help
albanello (Limited Experience)

????????????????????????????????????????????????????????????????
Here is the Stored Procedure/Query
????????????????????????????????????????????????????????????????

 

wisemx

Well as stated, with my limited experiance I am not capable of doing what you suggest this is the best solution I could come up with. I don't know how you could "create a solution that works in code with the initial data values". Do you have a full understanding of what I am trying to do? The object is to go through a table adding one to each and EVERY cell and writing that value back to that cell. The table is 990 rows and 50 column. The value ineach cell is used to determine if the value should be written back to that table or if the second table should be updated. There are other thing going on but that is the biggest time consumer, in my opinion.How can I do what you suggest without Looping through the table cells row by row column by columnand dynamically creating the SELECT and UPDATE statements that change with each interation?

After receiving some suggestions it appears indexing may be the answer. I am in the process of learning how to implement using indexing. Unless someone else can suggest a better solution.

Thank You for your responce.

albanello

 

Bruce

Thanks for your concern. I'm sorry I did not get back to you sooner. I've been working with Support they bumpt me up to the system admistrator.

I Implemented the indexing on the while loop that was taking all the time and knocked the execution time down from 3.5 Minutes to 1.5 Minutes I still have some work to do but I wanted to see if that fixed my problem on the actual site. I was extremly disappointed when it did not improve, it still timed out. So I ran the stored procedure on my local computer in query analyzer with "EXEC MMUpdateWhiteBallSinceHit_SkipHitTbl 1, 56" and it took 1.5 Minutes. Then I connected with query analyzer to my database at discountASP and ran the same command it took 7 Minutes Seven Minutes . Thats when I decided to contact support. They asked for the stored procedure and arguments and they ran it and verified the time 7 Minutes. Remember it took 1.5 Minues on my machine. I don't understand that at all ! They had some suggestion to check, which I have, and I have responded to them. I'm waiting for there responce now I don't think any ofthe suggestionsexplain the problem we are seeing.

I don't know what to do next I don't understand why it takes long on discountASP server.

Can you tell me:
1) Is there some maintenance I should be performing myself on my DB at discountASP or do they take care of all that.
2) Every time I export my DB to discountASP does it fragment over time (I export using kb.discount artical Q10041 instructions
3) Can you think of anything that would cause this problem.

Any help is appreciated because I don't get the feeling support wants to support. I'm not asking them to fix my problem I'm asking them what is causing this large difference in time. So I can fixthe problem.

albanello