Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Maybe I need a SQL Service Consultant
Author: Dave Watts
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319418
> 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!
Author: Paul Hastings
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319416
Jochem van Dieten wrote:
> ColdFusion comes with DataDirect 3.x.
>
> http://adobe.com/go/wish/
or you might give jTDS a spin.
Author: Adrian Lynch
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319415
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 -----
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319413
----- Excess quoted text cut - see Original Post for more -----
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/
Author: Rick Root
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319412
----- 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
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319352
> 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/
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319351
> 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/
Author: brad
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319336
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 -----
Author: Judah McAuley
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319325
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 -----
Author: Paul Hastings
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319322
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.
Author: Rick Root
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319321
> 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
Author: Al Musella, DPM
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319309
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 -----
Author: Brad Wood
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319304
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.
Author: Judah McAuley
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319303
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 -----
Author: Rick Root
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319279
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
Author: Brad Wood
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319236
> 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
Author: Rick Root
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319231
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
Author: Rick Root
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319154
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
Author: Brad Wood
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319108
> 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
Author: Judah McAuley
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319105
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 -----
Author: Rick Root
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319102
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
Author: brad
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319098
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.
Author: Rick Root
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58726#319094
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
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
