Can anyone be so green?

• Rob Pelletier

  SSC Eights!

  Points: 804

  More actions

  May 24, 2007 at 12:29 pm

  #117299

  Well I can...

  I have been working in networking and server operating systems for over 10 years, and, as I'm sure most of you know, that is no help at all working with a SQL Server.

  I have a client with SQL 2000 SP4 on his Small Business Server 2003.  Their SQL-based accounting app always runs just fine, but another product they bought is VERY slow most of the time.  They have been with this database vendor for a while, but he has just  gone from being Access-based to SQL-based and things haven't been real rosey since.

  All the reading I've done so far assumes I know a lot more about database design than I do (which is nothing).  I called some "consultant" I found on the web and he didn't even want to look at it - he just wanted to sell my client a new system, from the ground up for about $75K to $125K. 

  I would like to gather enough evidence to demostrate that either the vendor's insistence that the product be put on its own box is reasonable, or show that there is something wrong with the way it was designed.

  I have yet to find a "... for Newbies" book that just give me a bit of background about how SQL databases (and SQL Server) work, and a few quick and dirty tips about problem-solving.  Everything pre-supposes a lot of knowledge, and I don't have the time to start taking a lot of courses or reading 1000's of pages.

  If anyone can steer me to some books or articles that will get me started, I'd appreciate it.

   

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  May 24, 2007 at 1:17 pm

  #708763

  I don't know what to tell you in terms of books to read, but a great starting place would be right here on SSC in the articles section.  The articles section is broken up by topic so it is easy to browse through to find an article that interests you.  Go to the Newbies forum here on SSC and read Steve Jones's admin for beginners thread.  One of the best ways for you to gain knowledge would be to ask questions here and get suggestions, tips, and things to look at to help answer your questions.  The hands on experience you can get from following the advice of other credible professionals could prove invaluable in your case.  Also, read SQL Server books on line.  It comes installed as part of your SQL Server instance.  You can get a whole lot of understanding on the basics of how SQL Server works there as well as intermediate and advanced topics. 

  One of the best things you could do is ask a specific question here on this forum and get feedback.  So is performance of this SQL Server instance the main issue here?  If so, can you tell us some of the symptoms that you are seeing that points to slow performance?  Please be specific and give examples.  If it is not performance, what is you main concern with this your SQL Server implementation?

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• Rob Pelletier

  SSC Eights!

  Points: 804

  More actions

  May 24, 2007 at 2:19 pm

  #708786

  Thanks for your reply, John.  I HATE to ask for actual help, preferring to RTFM whenever possible.  Trouble is, the manual is EVERYWHERE and who has the time to read everything?

  The users of this database are dispatchers for a trucking company.  The database was designed to hold infomration on the trucks, drivers, clients, locations etc. to allow them to easily track where their resoruces are needed and where they actually are.  Some of the output is used for maintenance schedules, invoicing and other stuff relevant to someone in the trucking business.

  When a dispatcher has to enter a trip, the database program needs to marry up the appropriate data:  client info, truck info, trailer info etc. etc.  When things are working as they should (which happens a fair amount) screen changes and information lookups are fairly quick.  However, far too often users complain that "the system is slow".  That means (I have discovered by asking around) that this particular program is responding so slowly it negatively affects their productivity.

  People are NOT complaining about slow Internet/network speeds, slow opening of Word or Excel documents, or slow performance of their accounting application (also SQL-based) - it is always relating to this one app.

  A while ago, the vendor put their database (about 500 MB in size) on an XP Pro workstation (512 MB RAM), and, using the MSDE, "proved" that the problem was in the server, as it worked fine on the XP system.  However, they could only connect 5 users on it, so it didn't prove much in my mind.  Besides, this database, at just over half a gig and less than 20 users, should fly on a SBS server with 4 GB of RAM and mirrored SCSI drives.

  I would love to learn enough to use some of the tools (Query Analyzer, Windows Performance Monitor, et al.) to at least intelligently discuss the issue with the database vendor.  I have to approach it professionally, as they will get REAL defensive if I suggest that the issue is design without a bit of evidence.  (I know I would!)  These folks have been pretty good to work with (as has our client) and I want to maintain a healthy relationship with everyone.

  I'll keep looking over this site - there seems to be something for everybody here.  I'll look around for that Newbies' forum and the Steve Jones admin for beginners thread.

  I've always managed to find out what I needed to know in this busniess by reading forums, books, articles and trying stuff out at home.  However, SQL Server isn't just a different topic -- it's a whole different world, with strange logic and calling on defferent skills and rules that don't apply elsewhere.  Feels like I was suddenly dropped into the middle of a different culture...

   

   

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  May 24, 2007 at 2:49 pm

  #708799

  Rob,

  I should apologize in advance for the length of this post, but I got going and did some cut/pasting from some of my home grown docs that I’ve created here and for my clients.  It seems to me like your main focus as you are reading articles/books/BOL/etc. should be on performance monitoring.  This is a HUGE topic.  I would recommend the following steps:

  1.     Perform Round 2 from the ‘short list’ of performance tuning.  For those tuning in here, I’m not sure if the credit for this short list goes to Remi, Rudy, Jeff M., but here it is: 

  a.     DBCC DBREINDEX

  b.     UPDATE STATISTICS (with FULL scan) for all tables

  c.     Exec sp_recompile for all tables

  d.     Exec sp_refreshview for all views

  2.     Next, you want to prove that your Small Buz. Server is adequate to handle the load your Trucking app is placing on it:

  a.     Run Windows Performance Monitor for a 2-3 hour period during your peak times.  You’ll want to capture some high-level metrics on the ‘basics’ of performance: CPU, Disk, Memory, SQL Server.  I usually go with the following counters on my first-go-around:

                                                      i.     Processor>% Processor Time>_Total

                                                     ii.     PhysicalDisk>Current Disk Queue Length>_Total

                                                    iii.     Memory>Available Mbytes

                                                   iv.     SQLServer:Access Methods>Page Splits/sec

                                                     v.     SQLServer:SQL Statistics>Batch Requests/sec

                                                   vi.     SQLServeratabases>Log Flushes/sec

                                                  vii.     SQLServer:Buffer Manager>Buffer cache hit ratio

                                                 viii.     SQLServer:Buffer Manager>Page life expenctancy

                                                   ix.     SQLServer:Buffer Manager>Lazy writes/sec

                                                     x.     SQLServer:Buffer Manager>Checkpoint pages/sec

                                                   xi.     SQLServer:Memory Manager>Total Server Memory (KB)

                                                  xii.     SQLServer:Memory Manager>Target Server Memory (KB)

  2. Have your users keep track of times of ‘slowness’ so you have somewhere to look in the file.  Go through the perfmon data and look at these slow periods.  Here’s my ‘short list’ on Perfmon counters and thresholds:

                                                      i.     % Processor Time:  This is a measure of how busy the CPUs are.  It is OK to see spikes up to 100 % as long as they are only spikes and not sustained periods of time above 75 %– 80%.  Levels consistently at or above 75% reflect signs of performance degradation due to a CPU bottleneck . 

                                                     ii.     Disk Queue Length:  This is a measurement of how many processes are waiting (in queue) for disk resources.  Levels consistently above 2 processes per disk reflect signs of possible performance degradation due to I/O bottleneck.

                                                    iii.     Batch Requests/sec:  This is a measurement of overall SQL Server busyness.  Levels consistently near 1000 batch requests per second reflect a very busy SQL Server.  This is a relative number used to gauge levels of activity. 

                                                   iv.     Log Flushes/sec:  This measurement shows how often transactions are written from the log cache to the transaction log.  This measurement is a relative number and is used to gauge transaction throughput levels.

                                                     v.     Buffer Cache Hit Ratio:  This measurement shows how often SQL Server can satisfy a request without having to go to the disk.  Requests handled in memory are less resource intensive and perform much faster due to the absence of the overhead caused by disk I/O.  This measurement should be as close to 100% as possible.  Levels consistently below 90% - 95% show signs of a memory bottleneck. 

                                                   vi.     Page Life Expectancy:  This is a measurement of how long SQL Server estimates that a data or index page will remain in memory.  The longer a page can remain in the memory buffer, the more likely it is that SQL Server will not have to go to the hard disk to satisfy a request.  Values consistently below 300 indicate signs that SQL Server performance would benefit from more memory.  Low values here mean that SQL Server has a less likely chance for a Buffer Cache Hit because the pages are not staying in the buffer cache long enough.

                                                  vii.     Lazy Writes/sec:  This is a measurement of how often SQL Server writes dirty data pages from the buffer cache to disk in order to free up cache space for other processes.  This measurement should remain as close to zero as possible.  Levels consistently above 20 writes/sec indicate a possible need for more memory. 

                                                 viii.     Checkpoint Pages/sec:  In order to keep recovery time at a minimum, SQL Server creates checkpoints at which time all dirty data pages are written to disk.  This is a measurement of how many pages per second SQL Server’s checkpoint process moves to the hard disk.  It is normal to see spikes in this value during a checkpoint.  This measurement is mostly used to determine checkpoint frequency.

  3. If you are seeing ‘pressure’ in any of these areas, your server could be the issue (although I’m guessing it is not).

• Next, you’ll want to use SQL Profiler to capture the SQL traffic from the application, including execution plans, for performance analysis on indexing/t-sql/DDL design.

1. Set up a SQL Profiler trace to run and capture 2-3 hours worth of data (this could be a large amount of data).  Make sure you run SQL Profiler on a workstation with SQL Server client tools installed and plenty of disk space.  Try not to run this on your database server!  I like to monitor the following events:

                                                    i.     Errors and Warnings: all  events

                                                   ii.     Locks:

1.      Timeouts

2.      Deadlocks

                                                  iii.     Performance: execution plans

                                                 iv.     Security Audit:

1.      Audit Backup/Restore Events

2.      DBCC Events

                                                   v.     Stored Procedures

1.      RPC: Starting

2.      RPC: Completed

3.      SP: Staring

4.      SP: Completed

5.      SP: StmtStarting

6.      SP: StmtCompleted

                                                 vi.     T-SQL

1.      SQL: StmtStarting

2.      SQL: StmtCompleted

2. Go through the trace file.  I find the best way to do this is to save the trace off into a trace table so that I can bang against it with SQL commands.  You can then query the table for TOP 25 Duration and so on.  Look at the execution plans from the trace, serch the TextData column for table and index scans.  You can report these back to your vendor if you find poor performance plans and long durations during your slow times. 

 

 

 

 

 

 

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden