Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Join Date
    Aug 2005
    Posts
    21

    Unanswered: Help with SLOW SQL Server performance

    I'm still new to SQL Server so some of my lingo/verbage may be incorrect, please bare with me.

    The company I work for relies strictly on ASP and SQL Server for 85% of it's daily operations. We have some Access projects and some VB projects as well, but for the majority it's ASP and SQL Server.

    Previously we had 2 T1 lines with something like 3MB a piece and a handfull of Dell Servers. Our main server is also a Dell running Windows Server 2003 and is hosted through a reputable company here in town. They have a host of fiber lines running all over so I know we're getting good throughput. We've actually just upgradded to a DS3 but we're still working out the kinks with that. Anyway, I just want to eliminate that up front - we have great connection speeds.

    The problems lies, I believe in our database design. The company supposedly had a DBA come in and help setup the design some 3 or 4 years ago, however even with my limited knowledge I feel like something is just not working right.

    Our main table is "Invoices" which is obviously all of our Invoices, ever. This table has an Identity field "JobID" which is also the Clustered Index. We have other Indexes as well, but it appears they're just scattered about. The table probably 30-40 fields per row and ONLY 740,000 rows. Tiny in comparison to what I'm told SQL Server can handle.

    However, our performance is embarassing. We've just landed a new client who's going to be brining us big business and they're already complaining about the speed of their website. I am just trying to figure out ways to speed things up. SQL is on a dedicated machine I believe with dual Xeon processors and a couple gigs of ram. So that should be ok. THe invoices table I spoke of is constantly accessed by all kinds of operations as it's heart of what we do. We also have other tables such which are joined on this table to make up the reporting we do for clients.

    So I guess my question is this. Should the Clustered Index be the identify field and is that causing us problems? We use this field alot for access a single Invoice at a time and from what I understand this makes it a good Clustered Index, because the index IS the jobID we're looking for. But when it comes time to do reporting for a client, we're not looking at this field. We just pull the records for that Clients Number. And we only have 1400 clients at this point. So if we were to make the "ClientID" field the Clustered Index, it would much faster to Zero in on the group of Invoices we wanted because the ClientID is ALWAYS included in our queries.

    But because a "DBA" came in to design this setup, everyone is afraid to change it. I guess it's hard to explain without people sitting here going through the code and look at the structures of all our tables - but I guess what I need is like a guide of what to do to easily increase performance on SQL Server and the proper use of Clustered and Non-Clustered Indexs and how to mix and match those.

    Sorry I wrote a book.
    Ideas? This place has always helped me before, so thanks in advance!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The first lesson (ok, first after the "it is not your data" lesson), is to fix problems, and not theories. In order to pin down the exact problem, you have to amass approximately one mountain (mt) of data. Fire up perfmon on both the web server and the DB server. On both look at the following counters:

    Memory: pages/sec should be < 100
    Processor: %utilization should be < 75%
    System: Processor Queue Length should be < 2 * number processors you have
    System: Context Switches/sec should be < 10,000

    If any of these are significantly out of whack, then you can try to drill down into any one of those.

    On the database server side, Profiler and Query Analyzer are going to be your best friends. Trace
    Stored Procedures: RPC Completed and
    TSQL: SQL BatchCompleted
    Look for anything with a duration over 1000. If you see a particular procedure/query commonly running over 1000, then start working over that query. A query that is run once or twice a day is not much to worry over.

    In Query Analyzer, use sp_who2 to monitor for blockinig occasionally, and especially during slow times. It could be you just have some interface process that is locking up tables at times throughout the day.

    Short of writing a book, this should get you started.

  3. #3
    Join Date
    Aug 2005
    Posts
    21
    MCrowley - Thanks for posting back and offering me a starting point on actualy fixing visual problems instead of guessing on what may be the cause. I'll look into the suggested tools and post back on my findings.

    Much appreciated.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Who's the Admin of the Box?

    The FIRST thing I would do is to make sure all of your backups and transaction log dumps are ok and successful, by restoring the dumps to a test box.

    Do you have restore procedures in place to recover in case of a problem? No? Thatw ould be my second task.

    Do the developers use sa to build their code?

    I'd revoke all sa access to the database as my third step and make sure only 1-3 individuals hav sa authority....hopefully non developers.

    OK, so 4th I'd check for blocking (so_who2 active) and I'd also look at the locks (sp_locks). If you see a lot of table locks, you have a problem...most likely poorly design code.

    I would the run a trace and make sure the trace writes to a sql server table.

    Here's a template I made up. Make sure you change the host in the filter to a machine id that you are going to test on. Just make sure you change the extension back to .tdf.

    I would however seriusly recommend that you hire a qualified dba.

    what part of the world are you from?

    I got some spare evenings....got some virtual PC going over there?
    Attached Files Attached Files
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2005
    Posts
    21
    The box is administrated by the hosting company primarily. We have limited access. It is our server, but it's on their network in their racks so they handle most of those adminstrative tasks such as updates/hardware/etc. Myself and the rest of the IT dept (6 of us now) all Domain Administration access in Active Directory and access to modify the DB as we see fit. But we don't have an in-house DBA and the guy who HAD the most knowledge got canned for just reasons. So...

    We have Vertias in place for Back-Ups that are run every night. I don't know the ins and outs of whether they're full back ups or sequential or what have you, but I do know they work and we recently had one of our staff accidentally omit a where clause on an update which required us to restore a recent backup - so yes, that is tested and working just fine.

    Developes us sa to build code? I'm assuming you mean the sa account in SQL Server, but again I could be wrong. I'm learning as I go here. There was "master" account created if you will that is used to access the server throughout all of our production and test code. This is not the "sa" account, but one that was created. When we develop code, we have our own logins and access rights and typically just right the code right in ASP or use Query Anaylzer to test first. When the code is put into production, it uses a general connection string using the master account I spoke of. I hope that answers this.

    We do get blocking from time to time, but it's not every day. Still, it happens more often that I'd like. We also get frequent dissconnects. The company we were originally hosted with (we're 90% in house now except for this Server box) claims it's our Firewall and Router. We have no problems with lost connections from anywhere else so we fell this is an issue on their end. But getting back to the point. We do occassionally have blocks. I haven't really investigated them myself, but I can do that.

    I've been running a few traces since the first suggestions posted and the trace gets to 15,000 rows in less than a minute. Using the Duration template, I've got to scroll up several pages from the bottom to stop seeing durations over 1000 so there's obviously some performance gains to be made there. Our worst one is a SP that routinely goes over 11,000 for the duration and is ran frequently.

    Finally, I'm in the US - OHIO to be exact and we obviously do need a DBA. We've discussed doing that but would like to fix what we can on our own. So we can learn this stuff, and cut down on the cost and time the DBA will actually need to be here.

    We do have and VPN and etc setup, but obviously I can't being giving out that kind of info over a message board nor do I have the authority to give it out at all. The company is pretty private with their data. But I DO really appreciate the suggestions and I'll have a look and the attached template.

    Much thanks!

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well I would open up that stored procedure and run a test on it with a SHOW EXECUTION PLAN ([CTRL]+K) and look at the results. Look for scans of any kind.

    It could also be that you have a bad plan in PROCACHE. If it is a bad plan you'll need to recompile it.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OHIO! Blind dude, get over there and help him out....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Aug 2005
    Posts
    21
    Okay now this is where you've exceeded my brain capacity. I executed the SP in Query Analyzer and did the (CTRL + K) thing for showing the execution plan. I also just selected that from the action drop down, but I'm not getting anything so please teach me, wise one!

    Also how can I check my PROCACHE. Sigh, I can just a see a link to Amazon.com for "SQL Server for dummies" coming my way.

    Thanks again for all your help thus far.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you did both (selected it, then did CTRL+K), then I would most likely say that you turned it off.

    Just go to the menu and select then run the sproc.

    I did think you can see the cache, but you can clear it out. DBCC FREEOROCACHE...but that's not a good thing since it will cause everything to have to be recompiled......wich will slow everything down the first time it's called.

    Here's a test.

    Make sure the SHOWPLAN is on

    Execute the procedure in Query Analyzer...not the length of time and look at the plan

    Then do sp_recompile <sprocname>

    Execute the proc again. It will take longer because of the compil

    Execute the sproc again, then note the time.

    IF it is much fatser, then it's a bad plan.

    If it's not then it still could be a bad plan and it has no choice.

    You might want to show us the sproc.

    But tell us what the plan says first

    I'm kicking out, but I'll check in in the AM
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Aug 2005
    Posts
    21
    The Execution Plan drew out a huge grid diagram with icons and such. Is there anything specific you're looking for?

    SPRoc - 1st Run. 40 seconds
    SPRoc - 2nd Run. 28 seconds

    --Force Recompile
    SProc - 1st Run. 1 Mintue 28 seconds
    Sproc - 2nd Run. 2nd Run. 30 seconds??
    Sproc - 3rd, 4th, 5th, subsequent runs 7 - 15 Seconds!

    Recompliling the SP seems to have helped, but with your logic that means I have a bad plan?? But I don't even know what a plan is. I'll do some studying and research tonight in prep for more respones.

    Cheers!

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You'll be looking for any scans in the output. Also check for wide arrows (indicates many rows returned). Any large table that is getting scanned is probably a bad thing, unless this is a data warehouse/reporting kind of application. Cursors in the stored procedure are also a killer, and usually end up being the first target of any code re-writes.

    If you can post the code of the stored procedure in question, we can take pot-shots at suggesting indexes, but those will depend pretty heavily on your data distribution. Have you tried the Index Tuning Wizard? It should be available in Profiler, and definitely should be in Books Online (which is SQL Server for SQL Server users). If you don't have Books Online (aka BOL) then find it on Microsoft's website, download, and install. We may sound like we know it all, but we cheat. We have BOL open from the minute we get in, to about 2 minutes before we leave.

    Wait. Did I say that with my inside voice, or my outside voice?

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    <div style=Columbo>
    Oh, one other thing...
    </div>

    If you can see blocking, odds are it is a problem. Get to know dbcc inputbuffer (SPID), and try to chase down the blockers. The blockers could be half (or more) your problem.

  13. #13
    Join Date
    Aug 2005
    Posts
    21
    Code:
    CREATE PROCEDURE [dbo].[Envox_AdjustCallLog] 
    
    AS
    	
    	UPDATE Envox_CallLog SET Adjusted=0 
    	WHERE Adjusted=1 AND ABS(DATEDIFF(minute,StartTime, StartTimeAdj)) > 200
    
    	UPDATE 
    		Envox_CallLog 
    			SET StartTimeAdj=CallStartTime,
    			EndTimeAdj=CallEndTime,
    			Adjusted=1
    	FROM Envox_CallLog  INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID 
    		INNER JOIN Properties ON Invoices.PropID = Properties.PropID
    		INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
    	WHERE ZipCodes.TIME_ZONE='EST' AND Adjusted=0 AND Completed=1
    
    	UPDATE 
    		Envox_CallLog 
    			SET StartTimeAdj=DATEADD(hour,-1,CallStartTime),
    			EndTimeAdj=DATEADD(hour,-1,CallEndTime),
    			Adjusted=1
    	FROM Envox_CallLog  INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID 
    		INNER JOIN Properties ON Invoices.PropID = Properties.PropID
    		INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
    	WHERE ZipCodes.TIME_ZONE='CST' AND Adjusted=0 AND Completed=1
    
    	UPDATE 
    		Envox_CallLog 
    			SET StartTimeAdj=DATEADD(hour,-2,CallStartTime),
    			EndTimeAdj=DATEADD(hour,-2,CallEndTime),
    			Adjusted=1
    	FROM Envox_CallLog  INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID 
    		INNER JOIN Properties ON Invoices.PropID = Properties.PropID
    		INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
    	WHERE ZipCodes.TIME_ZONE='MST' AND Adjusted=0 AND Completed=1
    
    	UPDATE 
    		Envox_CallLog 
    			SET StartTimeAdj=DATEADD(hour,-3,CallStartTime),
    			EndTimeAdj=DATEADD(hour,-3,CallEndTime),
    			Adjusted=1
    	FROM Envox_CallLog  INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID 
    		INNER JOIN Properties ON Invoices.PropID = Properties.PropID
    		INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
    	WHERE ZipCodes.TIME_ZONE='PST' AND Adjusted=0 AND Completed=1
    
    	UPDATE 
    		Envox_CallLog 
    			SET StartTimeAdj=DATEADD(hour,-5,CallStartTime),
    			EndTimeAdj=DATEADD(hour,-5,CallEndTime),
    			Adjusted=1
    	FROM Envox_CallLog  INNER JOIN Invoices ON Envox_CallLog.JobID = Invoices.JobID 
    		INNER JOIN Properties ON Invoices.PropID = Properties.PropID
    		INNER JOIN ZipCodes ON LEFT(Properties.Zip,5) = ZipCodes.Zip
    	WHERE ZipCodes.TIME_ZONE='PST-2' AND Adjusted=0 AND Completed=1
    GO
    We have an Envox telephony Server in house. This SP is apparently adjusting all the logged call times based on their time zones. This is just one of many problem queries that are taking a while to run. I imagine the 3 joins per update on top of the ZipCodes table which is probably huge is the main issues.

    I'm also not the phone guy, so I'd have to suggest any recommned changes to him as I'd be eaten alive if I messed with it on my own. The index tuning Wizard is running as we speak, so hopefully it comes back with some good suggestions.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I can tell you right now, you're gonna see a scan on Properties

    ON LEFT(Properties.Zip,5) = ZipCodes.Zip

    Do this for us. Run the code below and post the Results

    Code:
    EXEC sp_spaceused Properties
    GO
    EXEC  sp_spaceused ZipCodes
    GO
    EXEC  sp_spaceused ZipCodes
    GO
    I can also tell you that I know what he's doing, he's turning everything into GMT Times. Now I've never been a big fan of derived columns, but man this is isure where this would be a good thing. Hell I might even say a TRIGGER might have been better than here. Also a derived column for the zip code so they don't need to use LEFT on the zip column in properties. That's called a stage 2 or nonsargable predicate and will always cause a scan.

    Also, do you know how to script a table and get the DDL in Enterprise Mangler? Look at the sticky at the top of the forum here and read the instructions if you don't.

    I betcha we can fix it up.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oh, and I like the error handling he has in there as well...is this run from and interface or a scheduled batch job...and I wonder about the very arbitrary

    UPDATE Envox_CallLog SET Adjusted=0
    WHERE Adjusted=1 AND ABS(DATEDIFF(minute,StartTime, StartTimeAdj)) > 200

    It's gotta be batch...

    He really needed to have a batch window table so he doesn't miss anything...here's the problem with the way he's doing it...if the proc doesn't run for a while...there's no determining how long this damn thing will run...he's just leaving the earliest inserts alone....

    Matt, wadda ya think, 2 mil inserts a day, TRIGGER? or Derived Column?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •