May 10, 2009 at 7:57 am
Hi,
Got a simple query using user defined scalar functions that splits names & addresses based on a wildcard across 1,000,000 rows.
On a VM single processor 16gb 64-bit sql 2005 (v3075) box or 32-bit SQL 2005 3gb XP (SP3) PC it run's in about 45 seconds.
When we run it on our production cluster (64gb, 16 cores, SQL 2005 ENT 64-bit v3175) initially it run's in about 30 seconds. The server is using 1gb of RAM for SQL and the 16 cores are not being stressed. The box only runs SQL and it's doing nothing else apart from this query when we test it. The disk queues, CPU and other perfomance counters are doing nothing.
Run it up 2-3 times and it's fine. The it goes upto 2.5 minutes and the only way to get it back to 30 seconds is to restart SQL service. Tried it on both nodes and makes no difference.
On the VM and local PC it always run's in about 30 seconds.
I'm stumped on this one - anybody got any ideas?
May 10, 2009 at 8:34 am
Please post udf and the exec plans (from both the VM and the prod box), as well as table defs and index defs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2009 at 6:37 pm
There's not really enough information here to provide anything useful straight off the bat.
Some questions however;
a) why is your physical cluster only using 1GB of 64GB of RAM?
b) are the storage layouts the same between the VM and physical cluster? (I'm assuming the XP PC is a single disk)
One thing that I'd check first up, does your UDF have a memory leak?
As Gail suggested, what are the execution plans - are there differences?
--
Andrew Hatfield
May 11, 2009 at 2:04 am
Thanks for the replies.
The only index on the table is on a unique identifier column which is not included in the query.
The cluster is set to use 10240mb min memory and 61440mb max memory. All processors are set to be used (both columns ticked). Max degree of parallelism is set to 0. Cost threshold is set to 0.
Attached is the scalar function, execution plans & client statistics for cluster & xp.
The query I am running is now in the attachment at the top.
May 12, 2009 at 5:47 am
Gail,
Hopefully all the information you require is in the attachment.
Thanks.
May 12, 2009 at 7:00 am
Jason,
Let me start by saying that I have no idea why it run so much more slowly in one case than another. Having said that:
You should use a faster string splitting method. Tally/numbers table solutions work well, CLR solutions are much faster still. See http://www.sqlservercentral.com/Forums/FindPost705627.aspx
Your current method may be the least efficient way to split a string I have yet encountered. (Sorry!)
The UDF should be defined as WITH SCHEMABINDING. It will be marked as non-deterministic otherwise.
A T-SQL UDF cannot generate a parallel plan. A CLR UDF can (unless a MAX datatype is used as a parameter). A tally/numbers table solution is also parallelizable.
You should use column widths appropriate to the real data rather than a fixed size of 2000.
Use VARCHAR (1 byte per chacter) rather than Unicode (NVARCHAR - 2 bytes per character) unless you *really* need Unicode. This applies to column definitions and the UDF parameters.
Your UDF returns a SMALLINT which is implicitly converted by SQL Server to an INT every time for use with SUBSTRING.
Be careful running DROPCLEANBUFFERS and FREEPROCCACHE - these affect the whole server, not just your database! (From the MSDN thread)
The table name in your query is not prefixed with a schema.
...
I think that is the main things, but my advice is simple:
1. Use a tally or CLR string splitter to allow a parallel plan.
2. Fix the table definition
Just those two things will result in performance which will astound and amaze :c)
Cheers,
Paul
P.S. I have also posted this on your original thread on the MSDN forums.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply