SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to Write a Runaway Query?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BobRoberts
Posting Yak Master

USA
102 Posts

Posted - 05/10/2013 :  10:12:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 05/10/2013 :  10:25:27  Show Profile  Reply with Quote
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 10000

SELECT * FROM #tmp a CROSS JOIN #tmp b CROSS JOIN #tmp c
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4351 Posts

Posted - 05/10/2013 :  10:48:32  Show Profile  Reply with Quote
Do you just want to to run for a long time or do you want to kill the server?
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 05/10/2013 :  13:42:33  Show Profile  Visit jackv's Homepage  Reply with Quote
you could use a LOOP , that never satisfies the condition to exit

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

BobRoberts
Posting Yak Master

USA
102 Posts

Posted - 05/16/2013 :  14:23:19  Show Profile  Reply with Quote
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?

Go to Top of Page

BobRoberts
Posting Yak Master

USA
102 Posts

Posted - 05/16/2013 :  14:29:59  Show Profile  Reply with Quote
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?

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000