Re: Maybe I need a SQL Service Consultant...

2009-02-17 Thread Dave Watts

> 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...

2009-02-17 Thread Paul Hastings

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...

2009-02-17 Thread Adrian Lynch

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...

2009-02-17 Thread Jochem van Dieten

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...

2009-02-17 Thread Rick Root

On Sat, Feb 14, 2009 at 6:45 PM, Paul Hastings  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...

2009-02-15 Thread Jochem van Dieten

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...

2009-02-15 Thread Jochem van Dieten

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...

2009-02-14 Thread brad

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 
Date: Sat, February 14, 2009 6:41 pm
To: cf-talk 


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  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...

2009-02-14 Thread Judah McAuley

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  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...

2009-02-14 Thread Paul Hastings

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...

2009-02-14 Thread Rick Root

On Sat, Feb 14, 2009 at 8:38 AM, 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?

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...

2009-02-14 Thread Al Musella, DPM

  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...

2009-02-13 Thread Brad Wood

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...

2009-02-13 Thread Judah McAuley

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  wrote:
>
> On Thu, Feb 12, 2009 at 11:34 AM, Brad Wood  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...

2009-02-13 Thread Rick Root

On Thu, Feb 12, 2009 at 11:34 AM, Brad Wood  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...

2009-02-12 Thread Brad Wood

> 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...

2009-02-12 Thread Rick Root

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  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...

2009-02-11 Thread Rick Root

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


Re: Maybe I need a SQL Service Consultant...

2009-02-09 Thread Brad Wood

> 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 hap

Re: Maybe I need a SQL Service Consultant...

2009-02-09 Thread Judah McAuley

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  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...

2009-02-09 Thread Rick Root

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...

2009-02-09 Thread brad

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 
 Date: Mon, February 09, 2009 6:39 pm
 To: cf-talk 
 
 
 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