Weird performance pattern - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Weird performance pattern

Author

Topic

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-07 : 23:40:20

So I've been running some very large queries, and what's been happening is it uses up all the memory and disk IO it can for a long item, then it just slows down to nothing.

Its sitting at 1mb\s right now off two SSD drives and processor usage is going between 25-48%.

Is this what happens when TempDB isn't big enough?

Any ideas?

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-08 : 00:40:57

name minimum maximum config_value run_value
access check cache bucket count 0 65536 0 0
access check cache quota 0 2147483647 0 0
Ad Hoc Distributed Queries 0 1 1 1
affinity I/O mask -2147483648 2147483647 0 0
affinity mask -2147483648 2147483647 0 0
affinity64 I/O mask -2147483648 2147483647 0 0
affinity64 mask -2147483648 2147483647 0 0
Agent XPs 0 1 1 1
allow updates 0 1 0 0
backup compression default 0 1 0 0
blocked process threshold (s) 0 86400 0 0
c2 audit mode 0 1 0 0
clr enabled 0 1 0 0
common criteria compliance enabled 0 1 0 0
contained database authentication 0 1 0 0
cost threshold for parallelism 0 32767 5 5
cross db ownership chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
Database Mail XPs 0 1 0 0
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
default trace enabled 0 1 1 1
disallow results from triggers 0 1 0 0
EKM provider enabled 0 1 0 0
filestream access level 0 2 0 0
fill factor (%) 0 100 0 0
ft crawl bandwidth (max) 0 32767 100 100
ft crawl bandwidth (min) 0 32767 0 0
ft notify bandwidth (max) 0 32767 100 100
ft notify bandwidth (min) 0 32767 0 0
index create memory (KB) 704 2147483647 0 0
in-doubt xact resolution 0 2 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32767 0 0
max full-text crawl range 0 256 4 4
max server memory (MB) 128 2147483647 25000 25000
max text repl size (B) -1 2147483647 65536 65536
max worker threads 128 65535 512 512
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 25600000 25600000
min server memory (MB) 0 2147483647 25000 25000
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
Ole Automation Procedures 0 1 0 0
open objects 0 2147483647 0 0
optimize for ad hoc workloads 0 1 0 0
PH timeout (s) 1 3600 60 60
precompute rank 0 1 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 10 10
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
Replication XPs 0 1 0 0
scan for startup procs 0 1 0 0
server trigger recursion 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
SMO and DMO XPs 0 1 1 1
transform noise words 0 1 0 0
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
xp_cmdshell 0 1 0 0

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-07-08 : 01:33:35

Not necessarily. Do you mean it slows down during the query or when the query has finished.
How do you have the databases distributed? i.e data\ log\ tempdb separated out onto different drives.
Have you checked the Execution plan of the query? Is the query pulling back excessive amounts of data?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

pebkacbigdatauser
Starting Member

23 Posts

Posted - 2012-07-08 : 11:47:04

quote:
Originally posted by jackv

Not necessarily. Do you mean it slows down during the query or when the query has finished.
How do you have the databases distributed? i.e data\ log\ tempdb separated out onto different drives.
Have you checked the Execution plan of the query? Is the query pulling back excessive amounts of data?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

The query has not finished, the tempdb is on a different drive, the execution plan has no warnings, but it is going through a large amount of data.

When I checked the wait list, there were 4 or 5 cxpackets waiting, 1 for basically forever.

It just doesn't make any sense to me.

Is there a legitimate reason for a query to basically have i\o drop from 1gb\s to 1\mbs for hours?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources