StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy is this query slow the first time after I start the service?
text
Body
copied!<p>Ok. Here's what I try to run:</p> <pre><code>USE tempdb; SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 CROSS JOIN sys.objects s3 CROSS JOIN sys.objects s4; </code></pre> <p>This is one of those "make me a numbers table" queries.</p> <p>Here's the problem. If I run this immediately after the SQL Server service is (re)started, it takes forever. Not forever as in ten seconds and I want it faster. Forever as in, I let it go over two hours once by accident and still had to kill it. I'm thinking it just never <em>ever</em> comes back. And ordinarily it takes under two seconds on my machine to run this.</p> <p><strong>However</strong>, if I do this instead:</p> <pre><code>USE tempdb; SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 CROSS JOIN sys.objects s3; DROP TABLE Numbers; SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 CROSS JOIN sys.objects s3 CROSS JOIN sys.objects s4; </code></pre> <p>Then it works as you would expect — the first <code>SELECT</code> runs in under two seconds, as does the second. Why don't I just use the three-table version? Because there aren't enough entries in <code>sys.objects</code> for that number cubed to equal a million result rows. But that's not even the point anymore.</p> <p>Anyway, from here on, I can repeat that second <code>DROP</code> / <code>SELECT…INTO</code> as much as I want, no problem. Somehow that first three-table version made it ok forever. At least, till the next time the service is restarted and/or the machine rebooted. At which point, running that last <code>SELECT</code> again never comes back. Again.</p> <p>Here's where it starts getting even weirder. If I pare that first <code>SELECT</code> back to a two-table version:</p> <pre><code>USE tempdb; SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2; DROP TABLE Numbers; SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 CROSS JOIN sys.objects s3 CROSS JOIN sys.objects s4; </code></pre> <p><em>This also makes the second <code>SELECT</code> run forever.</em> As does a one-table version. Somehow, that three-table version is magical!</p> <p><strong>What is going on here? Why is this slow?</strong> </p> <p>(And before anyone points out that I'm creating a permanent table in <code>tempdb</code>, yes, I know. Changing to actual temp tables doesn't make any difference.)</p> <hr> <p>Added info:</p> <ul> <li>This is SQL Server 2012 Developer Edition</li> <li>Output of <code>EXEC sp_WhoIsActive @find_block_leaders = 1, @sort_order = '[blocked_session_count] DESC'</code> (scripted as XML so it can be read here) is:</li> </ul> <pre> <?xml version="1.0" ?> <RESULTS1> <RECORD> <dd hh:mm:ss.mss>00 00:10:45.066</dd hh:mm:ss.mss> <session_id>52</session_id> <sql_text>&lt;?query -- SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 CROSS JOIN sys.objects s3 CROSS JOIN sys.objects s4; --?&gt;</sql_text> <login_name><em>my own login name redacted</em></login_name> <wait_info>(99ms)LCK_M_X</wait_info> <CPU> 9,750</CPU> <tempdb_allocations> 713</tempdb_allocations> <tempdb_current> 702</tempdb_current> <blocking_session_id>NULL</blocking_session_id> <blocked_session_count> 0</blocked_session_count> <reads> 583,273</reads> <writes> 537</writes> <physical_reads> 50</physical_reads> <used_memory> 3</used_memory> <status>suspended</status> <open_tran_count> 2</open_tran_count> <percent_complete>NULL</percent_complete> <host_name><em>my own machine name redacted</em></host_name> <database_name>tempdb</database_name> <program_name>Microsoft SQL Server Management Studio - Query</program_name> <start_time>2013-11-23 23:48:19.473</start_time> <login_time>2013-11-23 23:47:47.060</login_time> <request_id>0</request_id> <collection_time>2013-11-23 23:59:04.560</collection_time> </RECORD> </RESULTS1> </pre> <hr> <p>More added info:</p> <p>Why I'm putting this in tempdb is that it's part of a script intended to be run on virgin installations, and tempdb is guaranteed to be there. As I said, changing to global temp tables does no different.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload