|
| We see some odd things in our DW from time to time. When a query is
submitted that needs more memory than what is available in SQL to perform
something like a large join, what does the engine do? I can only imagine it
can do one of three things.
Wait for other processes to release enough memory
Break the requested process down into smaller but less efficient processing
methods
Somehow make more memory available by scaling back other processes.
Occasionally in our DW we see large queries that are running (ie. consuming
CPU and DiskIO) that normally run for a short duration but sometimes run for
many times longer. When this occurs we generally look at available CPU,
disk queuing, and sysprocesses.
In the clearest example we recently experienced the following:
Using two identical 16 way 64 bit servers (MDOP=4, 32GB ram, SQL build
1014), one running production the other idle. We had a scenario where in
production there were four large stored procedures running. Disk and CPU
utilization were in the 20% range. We then started a 80 million row select
into. The source of which was two moderately complex select statements
together with a union. After running for three hours disk IO had steadily
risen but the task had not finished. We killed it and restarted the same
query on production and after an hour killed it a second time. Before the
third attempt we killed several of the processes that had been running most
of the day. The third attempt we started the same query on both servers.
The idle server finished in 40 minutes and the production finished about 5
minutes later. While the estimated query plans were always the same the
behavior in sysprocesses between the long runs and the short run were very
different. There was no indication of a blocking or unyielding schedulers.
Thoughts and suggestions?
Thanks,
Danny
|
|