Maybe I need a SQL Service Consultant

So I've mentioned it before... I have this site that's experiencing 15-20 seconds of "slow" every 12-17 minutes... depending on how busy the site is.  It's not "standardized" enough for me to think it's related to any kind of scheduled tasks on either the CF server or the DB server. Normally, each of the three instances on the main CF server hums along processing 5-8 requests per second with my average request time running around 200ms.  There are rarely more than 3 active requests at any given time. But during these regular 15-20 second slowdowns, my active requests spikes to whatever I've got the maximum set to in Fusion Reactor (currently 12, which I lowered from 20 last night).  JDBC time spikes too.  CPU usually dips during the slowdown and spikes afterwards (as it processes all the queued requests). The slowdown occurs at the exact same time on all three instances. I'd been hoping to blame this on inconsistencies in our disk (can't defrag, failed chkdsk.. we're migrating to a new server soon) but then I realized that the same slowdown happens on our forums/images server - at the same time - though it's less noticeable because that server only handles an average of 7 requests per minute over the course of a day.  It spends most of its time serving up images via plan ol' web server requests) GIVEN that these jdbc spikes occur at the same time on a different server, I think I can rule out coldfusion as being a problem, and maybe I have some kind of SQL Server problem.. I ran SQL Profilder and had ran the index tuning wizard and it recommended only 2 new indexes on one of our larger tables... but I really just have no idea how to figure out what's causing these spikes. It's almost like the SQL Server box is just locking up every 12-17 minutes for 10-20 seconds at a time. Does anyone know of any companies out there with expertise at debugging this kind of thing? -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark

This isn't an answer to your question, but I had some suggestions. What DB are you using?   If you're using SQL server on windows are are quite a few thing you can watch in perfmon. hard drive reads/writes lock wait times CPU usage Avg Disk sec/read, write There are near infinite reasons for "occaisional slowdowns" on a site, but I have had it happen when a cached execution plan would decide to ignore an index and start doing table scans. I've found similar slow downs by keeping a close eye on lock escalations and drive activity on the SQL servers.  If the slow downs are coming from your SQL server, it is imperative for you to find what is running at the time.  If all the servers slow down at the same time they might be held up by locks or slow drive performance.  (Where are your data files stored?  transaction logs? temp db?   Raid 5? 10?   how many disks? etc..) Let me know what your DB is.  If you're on SQL Server 2005, I have a web based tool I can dust off that shows you the running spids, locks, the statement being executed, and a link to the execution plan.  It also optionally ties in with SeeFusion to get request information. (Though I think you said you had fusion reactor.) When it comes to diagnosing slow downs-- you can only be as good as your ability to look under the hood and know what's going on. ~Brad So I've mentioned it before... I have this site that's experiencing 15-20 seconds of "slow" every 12-17 minutes... depending on how busy the site is. It's not "standardized" enough for me to think it's related to any kind of scheduled tasks on either the CF server or the DB server.

SQL Server 2000 (we don't have a lot of money and the licence was cheaper... but we are planning to upgrade sometime soon) Anyway.. I just noticed the drive configuration on the server is C: and D: on one RAID array, and E: and F: on the second.  Which means my data files and OS files are all on the same set of physical disks, probably in a mirror (I've asked the person who ordered the server to get me the physical configuration, I'm not entirely sure how to find that info) So I'm using perfmon to monitor % disk read and write on the two physical sets of disks. WHENEVER there is a spike, I see the disk WRITE on the C/D drive jump to 100% while the disk read jumps up and down between 100% and 0%... during this time, I see disk activity on the E/F drive C:  OS and page file D:  SQL Server Data E:  SQL Server transaction logs F:  SQL Server backups It can't be a coincidence that when these spikes occur are also the ONLY time I see disk write activity to the E/F drives, where the transaction logs are. ALSO... I'm seeing errors like this during the slow periods: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 110) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. This kind of error message has only happened 8 times today... but maybe it's related.  I don't have a CLUE how to deal with SQL Server deadlocks! -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark

Contention deadlocks can be a royal pain to troubleshoot. You're going to need to find out what query was being run under that PID to see what it is causing it. A couple things here: Do you only have one application hitting that database or are there multiple? If two different applications are contending for a lock on the same table that can throw errors if not handled elegantly. What kind of transactions are you doing? I've run into this sort of problem before on a poorly designed app that had one big log table that logged info all the other tables and we had to change our transaction types to not do exclusive locks until we redesigned the application. Also, are you doing replication perchance? If it is set up to do log shipping that could explain the surge of activity on your logging disk. Judah ----- Excess quoted text cut - see Original Post for more -----

> SQL Server 2000 (we don't have a lot of money and the licence was > cheaper... but we are planning to upgrade sometime soon) Ok.  2000 doesn't give you near the info 2005 does, but it will give you quite a bit. First you need to keep an eye on when your performance spikes hit (which I think you're already doing) so you can try and get a handle on what is happening right then with the currently running requests. When a slow down starts, you need to get a snap shot of what was going on. First of all on your CF server.  Grab a full stack trace.  This is trivial with SeeFusion, and I assume Fusion Reactor as well.  You can scan through it to look for patterns.  Were multiple pages running the same query, etc? If Fusion Reactor allows you to log slow DB calls, turn that on with a threshhold low enough to log the stuff running at the time of the slow down. Now, for your SQL server. select * from master.dbo.sysprocesses http://msdn.microsoft.com/en-us/library/aa260456(SQL.80).aspx will return a record for every process on the SQL server.  This will tell you where the server the request is coming from (the DB doesn't know the client's ip-- only the web servers') usually I filter out sleeping processes and order by CPU desc.  Of course, if the problem is locks, then the cpu usage won't be high for the problem processes since they aren't doing anything but waiting. You can get the command the processes is running, but it is just basic info like "select", "update". Also in the sysprocesses table is the blocked column.  It contains the spid of any processes which are blocking it. If select * from master.dbo.sysprocesses where blocked > 0 returns a lot of records, you are dealing with a lot of locks.  Often times one process will chain down and block a number of requests all blocking others... you've got to keep climbing the lock tree to see who's blocking who until you find a process who is blocking other processes, but not being blocked themselves: select * from master.dbo.sysprocesses p1 where blocked = 0 and spid in (                     select blocked                     from master.dbo.sysprocesses p2                     where blocked > 0) I didn't test that, but I think it is right.  If you have one processes blocking everyone else, then you need to pull out sp_lock which is a system proc that shows you all the locks that exist in the database right then. You might get a TON of records back, but you need to know which ones to worry about. The spid column tells you what spid is holding or waiting for the lock. Cross references this with sysprocesses. Lock are a course of their own.  In general keep your eyes peeled for the following: A TON of RID locks for one process.  This may indicate a table scan or an update to many records at a time TAB locks are a lock on an entire table FIL locks can show occaisionally while writing a large tranny log file to a slow disk Of course, the lock type has to be taken in conjuction with the lock mode: Shared locks are generally harmless, but it depends on your isolation level. Exclusive locks are the ones to look out for. Lock status tells you if the lock is aquired or still being waited for. The database id and object id are your key to finding what the object (usually a table) was being locked. select * from sysdatabases will give you the database ids use databasename object_name(object_id) If you want to make your life easier, google for sp_lock2 which a proc that looks up the object names for you. Your goal is to find the pattern of what sql statements/tables are the repeat offenders and which ones are creating a large number of locks or leaving locks open for a long time before committing them.  If you are using transactions in your SQL you need to be careful how long you wait to commit those.  This would be bad: begin transaction update table with(holdlock) set col = foo -- run database maintneance plan... commit transaction Once you know what statement is giving you problems, take it and run it in query analyzer to get the real execution plan (not the estimated one). Reading an execution plan is a whole topic of its own, but you want to find the slowest parts of it.  Look for any table scans or extremely large amount of records being returned by one of the inner operations.  An index seek is most always better than an index scan. If you are doing a large amount of reads and the data doesn't need to be transanctionally safe, use with(nolock) which sets the isolations level for that statement to read uncommitted.  Make sure you are updating tables on an indexed key to avoid table scans which want to escalate to exlcusive table locks. ----- Excess quoted text cut - see Original Post for more ----- Whoa!  8 times?  Let's make it your mission to see that happen 0 times a day. :)  You're probably familiar with what a dead lock is.  Process 1 has resource and A and needs resource B.  Process 2 has resource B and needs resource A.  The resources are most commonly table, but can also be rows in a table if two processes have row or page locks on different parts of a table and want to escalate, but can't.  In general, if you have a series of tables you commonly update in a transaction, make sure you always update them in the same order.  If you are logging your errors and you know what queries are getting deadlocked then you can make a good guess what tables are the problem.  A lot of deadlocks can be avoided in the first place by making sure your locks stay open as short as possible.  This means calculating what needs to be changed, then getting in, doing the udpate/insert and committing quickly.  Large stored procs with a begin trans at the top and a commit trans at the bottom are bad news. At any rate, you don't have to just sit and guess what your dead locks are. Run a trace for deadlocks with SQL profiler.  It will tell you the two processes involved as well as the resource they were fighting over.  Use the object id to find the table in question.  That should help you track down where that is coming from. > WHENEVER there is a spike, I see the disk WRITE on the C/D drive jump > to 100% while the disk read jumps up and down between 100% and 0%... > during this time, I see disk activity on the E/F drive Where is your tempdb stored?  It would be nice to have your page file elsewhere, but it shouldn't be a big problem unless your server is tight on RAM and a lot of stuff is being swapped out to disk.  Look at Windows task manager to see the total amount of memory in use.  If it is greater that the amount of RAM installed, you are probably swapping badly. Find out what kind of RAID you have.  Mirroring provides the much needed redundacy, but no performance benifit until it is 10 or 01 which basically a mirred set of striped drives or vice versa.  Also, does your RAID controller have built-in RAM to cache commonly used data?  You will want to get a feel for what you hard drive activity normally looks like, to compare it to the levels you see when the database is misbehaving. Things like table scans on a few million records will cause a lot of read activity, but you seem to be getting a lot of write activity.  This is why I asked about your tempdb.  SQL server uses internal temp tables you don't even know about.  If you are doing some monster selects with a few dozens tables all joined at the same time, there might be some massive internal temp tables happening based on the indexes available.  This is where your execution plans come in and tell you what is happening. > So I'm using perfmon to monitor % disk read and write on the two > physical sets of disks. You might get some more meaningful data from the  Avg. Disk sec/Read and Write and Avg Disk Queue Length. Once again, there are about a billion things that factor into this, but for the first counter, you probably don't want to see your disks' avg reads and writes taking more than 50 ms.  A good value for this counter would be more like 10ms which means that the average read or write only takes 10ms to happen. The second counter is more complex and is based on your RAID or SAN configuration.  The basic rule of thumb is you don't want to see more than a small handful of queued I/O (think under 5-10) PER disk in your ARRAY.  An array of 20 disks with an avg disk queue length of 40 under load would be pretty good. (2 per disk) Of course, your hard disk performance is often the symptom, not the problem. You wouldn't want to go out and buy more drives, just because you had some poor SQL that wasn't taking advantage of your indexes. > It can't be a coincidence that when these spikes occur are also the > ONLY time I see disk write activity to the E/F drives, where the > transaction logs are. A lot transaction log activity probably means a lot of data is being updated/inserted somewhere.  Make sure updates only affect the necessary rows. Don't do "delete from #temp_table" when a truncate could do.  Well, it seems I have typed much more than I intended to.  I guess SQL server performance can be an expansive topic. Good luck, and let us know what you find. ~Brad

Well, using perfmon I was able to see significant amounts of disk read and write on the SQL Box during these times... but I still didn't really know what that meant. We've contracted a 4 hour block of time from a sql server tuning expoert and in conversations with him, just based on that information about, he suggested that we might have a problem with checkpoints, and it certainly seems to be that.  I finally figured out how to enable the sql server counters in perfmon (we're running 32bit sql server 2000 on 64 bit windows server 2003 so the counters didn't appear in the 64 bit perfmon)... it does look like a checkpoint problem.  I'll let the expert help us figure out the best way to resolve it but the solution may involve changing the server's recovery interval. Turns out that the physical hardware was ordered based on recommendations from seefusion.  I'm surprised they'd recommend having the OS and Data on the same physical set of disks (maybe they didn't and the hosting company just misinterpreted their recommendations) but at least we have the transaction logs on their own set of disks. Anyway, as I get a complete answer from our consultant I'll report back here in case anyone googles similar issues or if anyone is just curious. Rick

Final update on this issue for future generations.... Our SQL expert helped pinpoint some query issues and updated a few indexes for us... resolving the way my database driver was configured helped clear up a lot of problems by reducing the # of reads we were doing.  HOWEVER, the server slowdowns still occurred for the same duration but they were less noticable because more of the queries were runnign without reading from disk during these times. There are, apparently, two solutions to performance issues caused by checkpointing. #1 - add more disks to your drive array.  Apparently, a 6 disk array is less likely to have these kind of read/write blockages than a 3 disk array.  This isn't an option for us right now though. #2 - force checkpointing more often.  This was our solution.  The system was checkpointing every 10-20 minutes depending on activity, and we are forcing it now to do checkpoints every minute now through a SQL Agent task.  The spikes are still there, but they won't really affect our users anymore. Apparently, there is option #3 - which is "Upgrade to SQL Server 2008" which in and of itself would improve checkpointing performance, but also apparently SQL 2000 can only use 1.5gb of RAM, so we're underutilizing our server in terms of physical memory (it has 4gb total) Rick ----- Excess quoted text cut - see Original Post for more ----- -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark

> resolving the way my database driver was configured > helped clear up a lot of problems by reducing the # of reads we were > doing. @Rick, can you provide any specifics on the above change?  What did he change?  How did it reduce reads? Thanks. ~Brad

On Thu, Feb 12, 2009 at 11:34 AM, Brad Wood <brad@bradwood.com> wrote: ----- Excess quoted text cut - see Original Post for more ----- Sorry for having two threads on this issue but here are the specifics... Unicode support was enabled in the FR driver, and I'd created a member_sessions table with a "char" field as the primary key.. not nchar.  So for that reason, the indexes weren't working very well because of the data type mismatch... SQL ended up having to do a convert() and ended up doing an index scan instead of an index seek I guess.. something like that. So I disabled unicode support and then last night I converted my CHAR fields to NCHAR and re-enabled unicode support in the driver. -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark

That's fascinating. But why would sql server create an index in a codeset that didn't match the column? You'd think that the index would match the declared type of the column automatically. I would think of that as a bug in sql server. We're moving some applications to do more multilingual support soon and that sort of gotcha is something for me to keep in mind. Gotta convert everything to nvarchar. Thanks for giving us more info about the resolution. Judah ----- Excess quoted text cut - see Original Post for more -----

This isn't a bug in SQL Server.  Rick said that his primary key column was a char field and so was the index.  Since Unicode support was enabled, parameters were coming in as nchars or nvarchars. SQL Server cannot compare a char to an nchar so it must convert one so the data types match. http://www.codersrevolution.com/index.cfm/2009/2/13/SQL-Server-Gotcha-Implicit-Unicode-Conversion ~Brad > That's fascinating. But why would sql server create an index in a > codeset that didn't match the column? You'd think that the index would > match the declared type of the column automatically. I would think of > that as a bug in sql server.

  Wow!    I didn't know that.  So would it be safer to use all nchar fields in the database instead of char, then no matter what the paramater came in as, it is faster to convert the parameter to unicode once than to convert every value in the index? At 01:41 AM 2/14/2009, you wrote: ----- Excess quoted text cut - see Original Post for more -----

>   I didn't know that.  So would it be safer to use all nchar fields > in the database instead of char, then no matter what the paramater > came in as, it is faster to convert the parameter to unicode once > than to convert every value in the index? It would seem that way.  My rule of thumb is now: Always use nchar even if you know it will NEVER have unicode data in it (like you're storing a coldfusion UUID) because CF will do all unicode or all non-unicode. -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark

Rick Root wrote: > It would seem that way.  My rule of thumb is now: > > Always use nchar even if you know it will NEVER have unicode data in > it (like you're storing a coldfusion UUID) because CF will do all > unicode or all non-unicode. ..or you might look to a newer JDBC driver that knows about "n" datatypes. it's not cf, its the db driver.

----- Excess quoted text cut - see Original Post for more ----- I find it hard to believe that CF8's database drivers don't know about "n" datatypes. -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark

>> ..or you might look to a newer JDBC driver that knows about "n" datatypes. it's >> not cf, its the db driver. > > I find it hard to believe that CF8's database drivers don't know about > "n" datatypes. Support for national character datatypes was introduced in JDBC4. The first DataDirect release to support JDBC4 is DataDirect 4.x. ColdFusion comes with DataDirect 3.x. http://adobe.com/go/wish/ Jochem -- Jochem van Dieten http://jochem.vandieten.net/

Has anyone got an example of a wish list request coming into the language/server? Not being flippant, just interested is all. Adrian ----- Excess quoted text cut - see Original Post for more -----

> Has anyone got an example of a wish list request coming into the > language/server? > > Not being flippant, just interested is all. I can think of quite a few. Recently, the support of CFQUERYPARAM with cached queries, for example. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information!

Jochem van Dieten wrote: > ColdFusion comes with DataDirect 3.x. > > http://adobe.com/go/wish/ or you might give jTDS a spin.

> It would seem that way.  My rule of thumb is now: > > Always use nchar even if you know it will NEVER have unicode data in > it (like you're storing a coldfusion UUID) because CF will do all > unicode or all non-unicode. If you need to use MS SQL Server you should stop using UUIDs alltogether and just use GUIDs in your CFMl and the UNIQUEIDENTIFIER datatype in MS SQL Server. Jochem -- Jochem van Dieten http://jochem.vandieten.net/

>   I didn't know that.  So would it be safer to use all nchar fields > in the database instead of char, then no matter what the paramater > came in as, it is faster to convert the parameter to unicode once > than to convert every value in the index? That is the safe choice that will make your database a bit slower, but without any corner cases where your databases suddenly becomes several orders of magnitude slower. http://jochem.vandieten.net/2008/12/15/querying-ms-sql-server-guuids-from-coldfusion/ Jochem -- Jochem van Dieten http://jochem.vandieten.net/

So you are saying it wasn't that the index was a different codepage than the column but rather that the data stream had to be converted because the data was coming in as Unicode? I can see that. Obscure but I can see it. Judah ----- Excess quoted text cut - see Original Post for more -----

In short, yes.  Read my blog post that I linked to.  I explained the whole thing and even provided code samples you can use to reproduce it on your own server.  But basically you have it right-- the data in the table had to be converted one row at a time which defeated the index. ~Brad So you are saying it wasn't that the index was a different codepage than the column but rather that the data stream had to be converted because the data was coming in as Unicode? I can see that. Obscure but I can see it. Judah ----- Excess quoted text cut - see Original Post for more -----