Re: Maybe I need a SQL Service Consultant...
On Sat, Feb 14, 2009 at 6:45 PM, Paul Hastings p...@sustainablegis.com wrote: 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. 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 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319412 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Maybe I need a SQL Service Consultant...
On Tue, Feb 17, 2009 at 5:33 PM, Rick Root wrote: On Sat, Feb 14, 2009 at 6:45 PM, Paul Hastings wrote: ..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/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319413 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Maybe I need a SQL Service Consultant...
Has anyone got an example of a wish list request coming into the language/server? Not being flippant, just interested is all. Adrian -Original Message- From: Jochem van Dieten [mailto:joch...@gmail.com] Sent: 17 February 2009 16:41 To: cf-talk Subject: Re: Maybe I need a SQL Service Consultant... On Tue, Feb 17, 2009 at 5:33 PM, Rick Root wrote: On Sat, Feb 14, 2009 at 6:45 PM, Paul Hastings wrote: ..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/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319415 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Maybe I need a SQL Service Consultant...
Jochem van Dieten wrote: ColdFusion comes with DataDirect 3.x. http://adobe.com/go/wish/ or you might give jTDS a spin. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319416 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Maybe I need a SQL Service Consultant...
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! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319418 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Maybe I need a SQL Service Consultant...
On Sat, Feb 14, 2009 at 2:38 PM, Al Musella, DPM wrote: 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/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319351 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Maybe I need a SQL Service Consultant...
On Sat, Feb 14, 2009 at 10:42 PM, 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. 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/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319352 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Maybe I need a SQL Service Consultant...
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: 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. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319309 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Maybe I need a SQL Service Consultant...
On Sat, Feb 14, 2009 at 8:38 AM, Al Musella, DPM muse...@virtualtrials.com wrote: 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 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319321 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Maybe I need a SQL Service Consultant...
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. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319322 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Maybe I need a SQL Service Consultant...
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 On Fri, Feb 13, 2009 at 10:41 PM, Brad Wood b...@bradwood.com wrote: 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. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319325 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Maybe I need a SQL Service Consultant...
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 Original Message Subject: Re: Maybe I need a SQL Service Consultant... From: Judah McAuley ju...@wiredotter.com Date: Sat, February 14, 2009 6:41 pm To: cf-talk cf-talk@houseoffusion.com 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 On Fri, Feb 13, 2009 at 10:41 PM, Brad Wood b...@bradwood.com wrote: 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. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319336 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Maybe I need a SQL Service Consultant...
On Thu, Feb 12, 2009 at 11:34 AM, Brad Wood b...@bradwood.com wrote: 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? 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 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319279 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Maybe I need a SQL Service Consultant...
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 On Fri, Feb 13, 2009 at 6:16 AM, Rick Root rick.r...@webworksllc.com wrote: On Thu, Feb 12, 2009 at 11:34 AM, Brad Wood b...@bradwood.com wrote: 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? 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 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319303 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Maybe I need a SQL Service Consultant...
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. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319304 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Maybe I need a SQL Service Consultant...
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 On Wed, Feb 11, 2009 at 9:13 AM, Rick Root rick.r...@webworksllc.com wrote: 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 -- Rick Root New Brian Vander Ark Album, songs in the music player and cool behind the scenes video at www.myspace.com/brianvanderark ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319231 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Maybe I need a SQL Service Consultant...
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 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319236 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Maybe I need a SQL Service Consultant...
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 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319154 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319094 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Maybe I need a SQL Service Consultant...
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
Original Message
Subject: Maybe I need a SQL Service Consultant...
From: Rick Root rick.r...@webworksllc.com
Date: Mon, February 09, 2009 6:39 pm
To: cf-talk cf-talk@houseoffusion.com
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.
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319098
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Maybe I need a SQL Service Consultant...
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 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319102 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Maybe I need a SQL Service Consultant...
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 On Mon, Feb 9, 2009 at 6:35 PM, Rick Root rick.r...@webworksllc.com wrote: 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 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319105 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Maybe I need a SQL Service Consultant...
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. 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
