Professional Web Applications Themes

Performence - Microsoft SQL / MS SQL Server

Hi, Can I set the maximum I/O and memory usage when executing an transaction. The queries I'm executing are taking all the Power it needs leaving nothing to our business system. I have several SQL statements which I would like to run during working hours not during night. I have looked at the SET QUERY_GOVERNOR_COST_LIMIT but can't see what it really does. Thanks Tob...

  1. #1

    Default Performence

    Hi,
    Can I set the maximum I/O and memory usage when executing
    an transaction. The queries I'm executing are taking all
    the Power it needs leaving nothing to our business system.

    I have several SQL statements which I would like to run
    during working hours not during night.

    I have looked at the SET QUERY_GOVERNOR_COST_LIMIT but
    can't see what it really does.

    Thanks

    Tob
    Tobbe Guest

  2. #2

    Default Re: Performence

    Tobbe,

    query_governor_cost_limit will not help.
    This option will allow SQL Server to refuse to run a potentially greedy query
    i.e. It's all or nothing - Not just slow it down.

    The only configuration option I know of that may be of use
    ( and only then if you have SMP = multiple processors )
    is setting "max degree of parallelism " to limit the query to fewer processors.

    If your query has parts, you may be able to break it up using separate
    transactions and "waitfor delay", or look at breaking the rows it processes at a time
    up into batches ( using a more restrictive where clause ). I've found a sql agent
    job running a query bit at a time, by incrementing "where clause" parameters kept in a
    separate table, a useful technique.

    Regards
    AJ


    "Tobbe" <tobias.hasslebrantsundit.se> wrote in message news:046301c33fcc$c6411c60$a001280aphx.gbl...
    > Hi,
    > Can I set the maximum I/O and memory usage when executing
    > an transaction. The queries I'm executing are taking all
    > the Power it needs leaving nothing to our business system.
    >
    > I have several SQL statements which I would like to run
    > during working hours not during night.
    >
    > I have looked at the SET QUERY_GOVERNOR_COST_LIMIT but
    > can't see what it really does.
    >
    > Thanks
    >
    > Tob

    Andrew John Guest

  3. #3

    Default Re: Performence

    No, your best option is to either optimize your queries or run them on a
    reporting server. If you have a multi-processor system you can try setting
    the MAXDOP to 1 for your query to see if that helps.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Tobbe" <tobias.hasslebrantsundit.se> wrote in message
    news:046301c33fcc$c6411c60$a001280aphx.gbl...
    > Hi,
    > Can I set the maximum I/O and memory usage when executing
    > an transaction. The queries I'm executing are taking all
    > the Power it needs leaving nothing to our business system.
    >
    > I have several SQL statements which I would like to run
    > during working hours not during night.
    >
    > I have looked at the SET QUERY_GOVERNOR_COST_LIMIT but
    > can't see what it really does.
    >
    > Thanks
    >
    > Tob

    Andrew J. Kelly Guest

  4. #4

    Default Re: Performence

    This is just a restriction for estimated cost of the query. This option sets
    upper limit for the time in which a query can run this is the estimated
    elapsed time, in seconds, required to execute a query. you can change this
    value in 2 ways to make a serverwide setting you can use sp_configure to
    change the value of "query governor cost limit" to change the value for a
    connection make this setting using SET QUERY_GOVERNOR_COST_LIMIT . if any
    query is crossing this limit of configured value it gets aborted.

    --
    -Vishal
    "Tobbe" <tobias.hasslebrantsundit.se> wrote in message
    news:046301c33fcc$c6411c60$a001280aphx.gbl...
    > Hi,
    > Can I set the maximum I/O and memory usage when executing
    > an transaction. The queries I'm executing are taking all
    > the Power it needs leaving nothing to our business system.
    >
    > I have several SQL statements which I would like to run
    > during working hours not during night.
    >
    > I have looked at the SET QUERY_GOVERNOR_COST_LIMIT but
    > can't see what it really does.
    >
    > Thanks
    >
    > Tob

    Vishal Parkar Guest

Similar Threads

  1. #39852 [NEW]: static variable shows abnormal performence
    By eingmarra at hotmail dot com in forum PHP Bugs
    Replies: 1
    Last Post: December 18th, 08:36 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139