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.
| Author |
Topic |
|
BobRoberts
Posting Yak Master
109 Posts |
 Posted - 2013-05-10 : 10:12:17
|
This might seem like an odd request, but I need to know how to write a query that will take many hours, days, years to run. The reason I need to write one is we're testing an ODBC driver and want to see if the source server is able to register (and thus provide a means of ending) a runaway query unintentionally initiated through the ODBC driver. So if such a theoretical occurrence would happen in the future, the process could be killed before eating up source server resources.I guess what I need is a basic generic example of what one would look like - a SELECT statement. Note that the actual tables I will be modifying this generic example to fit do already contain hundreds of thousands of rows. I assume a runaway query could be launched by a particular type of "bad join" (Cartesian?), but I'm not clear on the exact mechanics/characteristics of such a join. But the simpler it is the better - as long as it accomplishes the end goal of never finishing, at least during this century.  |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-10 : 10:25:27
|
If you just want the query to wait, use something like this:WAITFOR DELAY '23:59'GO 1000 Or, if you want to crash the system, use something like this (This is just an example, be as aggressive with the 10000 or the number of cross joins, and the columns in the table.)CREATE TABLE #tmp (id INT IDENTITY(1,1));INSERT INTO #tmp DEFAULT VALUES GO 10000SELECT * FROM #tmp a CROSS JOIN #tmp b CROSS JOIN #tmp c |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-05-10 : 10:48:32
|
| Do you just want to to run for a long time or do you want to kill the server? |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-05-10 : 13:42:33
|
| you could use a LOOP , that never satisfies the condition to exitJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2013-05-16 : 14:23:19
|
That's a good question - I hadn't thought of the distinction, but there is a difference between just running a long time and generating intense compounding activity. I actually want to "kill the server". I'm working through ODBC and I want the other end to have to create massive amounts of temp files to accommodate the query. This would be done in strictly a test environment.quote:
Originally posted by Lamprey
Do you just want to to run for a long time or do you want to kill the server?
|
|
|
|
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2013-05-16 : 14:29:59
|
Looks like the CROSS JOIN may be the ticket to perdition that I'm seeking.quote:
Originally posted by Lamprey
Do you just want to to run for a long time or do you want to kill the server?
|
|
|
|
|
|
|
|
|
How to Write a Runaway Query?