SQL Server Performance Forum – Threads Archive
Need help!!
of course… who doesn’t Situation: same query, same data, same indexes on three instances… test instance… Dell Poweredge 4400 – 2 860 Mhz processors with 1024 MB of Total Physical Memory. – Local storage. Production instance Dell Poweredge 6850 – 4 3.0 Mhz Processors with 12,283 MB of total Physical Memory (5 GB allocated to SQL Server) – Fiber attached SAN storage on RAID 10 array. test 2 instance… Other active/active node of the same build as the production instance. We have a query that runs in almost 3 minutes on the production or test 2 instances, but it will run in under 11 seconds on the test instance. The query calls the same user defined function on all three instances. This user definied function basically parses an XML document that is stored in a text field. sp_configure from Test 1quote:affinity mask-2147483648214748364711
allow updates0100
awe enabled0100
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03211
max server memory (MB)42147483647905905
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364700
nested triggers0111
network packet size (B)5126553640964096
open objects0214748364700
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
scan for startup procs0111
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700
allow updates0100
awe enabled0100
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03211
max server memory (MB)42147483647905905
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364700
nested triggers0111
network packet size (B)5126553640964096
open objects0214748364700
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
scan for startup procs0111
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700
Production sp_configure
quote:affinity mask-2147483648214748364700
allow updates0100
awe enabled0111
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03200
max server memory (MB)4214748364751205120
max text repl size (B)021474836476553665536
max worker threads3232767350350
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364715031503
nested triggers0111
network packet size (B)5126553640964096
open objects0214748364700
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
scan for startup procs0111
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700
allow updates0100
awe enabled0111
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03200
max server memory (MB)4214748364751205120
max text repl size (B)021474836476553665536
max worker threads3232767350350
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364715031503
nested triggers0111
network packet size (B)5126553640964096
open objects0214748364700
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
scan for startup procs0111
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700
test 2
quote:affinity mask-2147483648214748364700
allow updates0100
awe enabled0111
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03200
max server memory (MB)4214748364751205120
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364710241024
nested triggers0111
network packet size (B)5126553640964096
open objects0214748364700
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836476060
remote proc trans0100
remote query timeout (s)0214748364700
scan for startup procs0111
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700
allow updates0100
awe enabled0111
c2 audit mode0100
cost threshold for parallelism03276755
Cross DB Ownership Chaining0100
cursor threshold-12147483647-1-1
default full-text language0214748364710331033
default language0999900
fill factor (%)010000
index create memory (KB)704214748364700
lightweight pooling0100
locks5000214748364700
max degree of parallelism03200
max server memory (MB)4214748364751205120
max text repl size (B)021474836476553665536
max worker threads3232767255255
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364710241024
nested triggers0111
network packet size (B)5126553640964096
open objects0214748364700
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote login timeout (s)021474836476060
remote proc trans0100
remote query timeout (s)0214748364700
scan for startup procs0111
set working set size0100
show advanced options0111
two digit year cutoff1753999920492049
user connections03276700
user options03276700
any help would be appreciated. I haven’t a clue as to where the problem could originate from.
Same statistics?
Same execution plan?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
Thanks for the path to look down, how can the execution plans be different?
One of the reasons, differents statistics.
If auto statistics is set on, in all instances, on production server you will have more statistics because is more used. Did you have same maintenance plan in all servers, like index defrag?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
I believe they are all set the same, to add further confusion I moved the backup to another instance of SQL apart from our cluster and the same query ran in 6 sec… the backup was taken from the Cluster db… the main differences in these two is boot.ini switches /3GB and /PAE and AWE is configured on the cluster.
I suggest to update statistics (with fullscan) on production server.
What you did (move) is a clue.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
Wouldn’t it have been corrected on the other active node of the cluster when it was moved there?
after 5 we took both instances off line, rebooted the cluster nodes, brought them back online… works like a champ…. :-S
further info… just to clear it up like mud… after the aboved mentioned reboot, after several tries of the query the slow response crept back in, so, we had the developer take the function out of SQL and place it in his asp code as a function there… response time went back to seconds….
]]>