Forum Replies Created

Viewing 15 posts - 24,961 through 24,975 (of 49,552 total)

  • RE: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near line 1 When running re-index script

    Intelligent indexing script: http://sqlfool.com

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 4:48 pm

    #1351374

  • RE: Where Clause

    Statement level.

    To get the plan optimisation for queries like the following, the recompile has to be a hint on the query: OPTION(RECOMPILE)

    SELECT ...

    WHERE (Col1 = @var1 OR @var1 IS NULL)

    ...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 2:22 pm

    #1351324

  • RE: Multiple OUTER JOIN query is slow

    pdanes2 (7/11/2011)

    GilaMonster (7/11/2011)

    pdanes2 (7/11/2011)

    You say index the table with included columns, but the primary key is clustered automatically (and not just by default, as you wrote earlier, but unchangeably so)

    By...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 2:00 pm

    #1351312

  • RE: Tools question (performance analysis)

    Database Tuning Advisor

    Be careful. Don't accept it's recommendations without testing them carefully. Implement ones that help, not all of them. It's a good place to start tuning, it's a bad...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 12:59 pm

    #1351288

  • RE: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near line 1 When running re-index script

    You might want to play it safe and put [] around the table name, in case you have some which are not valid identifiers or are reserved words.

    EXEC sp_MSforeachtable @command1=...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 12:54 pm

    #1351285

  • RE: Where Clause

    Ninja's_RGR'us (7/11/2011)

    I had forgotten about that one. Most of my reports have to use the WITH RECOMPILE because the date ranges vary too much.

    Because of that I'm...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 12:47 pm

    #1351282

  • RE: Speed up Index Creation time

    kramaswamy (7/11/2011)

    If I dropped most of the indexes, and created only a single long index, which contained all the foreign keys as indexed fields, would the query optimiser decide to...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 12:44 pm

    #1351280

  • RE: ReCompilation of Stored proc and Function

    only4mithunc (7/11/2011)

    So you mean to say that the multivalued table valued functions are pre-compiled ( i.e they get compiled during the first run ) ?

    As far as I recall, they...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 12:42 pm

    #1351279

  • RE: Multiple OUTER JOIN query is slow

    pdanes2 (7/11/2011)

    Family_Idx, defined one of two ways - Index or Unique Key - I still can't figure out the difference:

    There isn't much of one.

    You say index the table...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 12:41 pm

    #1351278

  • RE: Members of BUILTIN\Administrators not automatically members sysadmin?

    mmcginty (7/11/2011)

    which, logically would make any privileges/access granted to BUILTIN\Administrators valid, on any SQL Server, not good if you don't want anyone else to be able to attach a...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 12:37 pm

    #1351277

  • RE: Where Clause

    The plan compiled for that will have to have a check the second condition. That's because the plan has to be safe for reuse and if the plan had no...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 10:46 am

    #1351206

  • RE: sql server 2008 queries with intermittent poor performance

    jmaia (7/11/2011)

    Have you minimised locking in the procedures ??

    [jmaia] Yes. At least all the selects in the sp's use NOLOCK.

    Is that a good idea?

    http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 9:48 am

    #1351176

  • RE: Speed up Index Creation time

    Can be. Not as simple as that, but it's about giving the optimiser the option and seeing what it thinks.

    Back to the original question though, short of better hardware I...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 9:47 am

    #1351175

  • RE: sql server 2008 queries with intermittent poor performance

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 9:12 am

    #1351152

  • RE: Why Index Scan instead of Index Seek

    Though do note that OR and UNION may produce different results.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    July 11, 2011 at 9:08 am

    #1351149

Viewing 15 posts - 24,961 through 24,975 (of 49,552 total)