Blog

Get frustrated when you read conflicting opinions on the web? Me too — I can’t go to sleep when someone’s wrong on the Internet, but it’s tough to correct everybody. In this 30-minute session, I’ll explain the most common bad advice that I see, explain why it’s wrong, and show you how to set up your SQL Server for speed, not slowness.

↑ Back to top
  1. If you cannot get large enough SSDs for your SQL server; with a heavily used VLDB (1 TB) OTLP (several hundred read/write per second) system would it still be best to separate MDF, LDF and TempDB on separate drives?

    • Robert – I’m assuming you’re saying you’d use multiple solid state drives. In that case, you want to use a RAID array – protecting your data with redundancy on multiple drive. For that, just use RAID 10 and you can stripe your data across sets of drives.

  2. Hi Brent,

    you mention that MAXDOP and Cost Threshold for Parallelism should go hand in hand, but you do not recommend a value for that.
    Is 5 seconds best practice?
    What do i have to look for in order to get an idea how to set this value?

    Thanks, Christoph

  3. Hi Brent

    From what I know, a counter-measure for parameter sniffing one can use is to use a local variable for each parameter and instead using the actual parameter in the subsequent queries, the local variable be used. This way, query optimizer will not be able to “sniff” the first parameter value passed to this stored procedure.

    The code would look similar to this one:
    CREATE PROCEDURE dbo.usp_Search @State CHAR(2) AS

    DECLARE @vState CHAR(2)
    SET @vState = @State

    SELECT * FROM dbo.SalesHistory
    WHERE State = @vState

  4. We are populating a data warehouse each night into a separate database and then doing a name swap from the newly populated one to the production one. This limits the amount of downtime the warehouse can be done. Unfortunately, we have found we have to run DBCC FLUSHPROCINDB in order to reset the large number of procedures used to reference the newly swapped in db. Are there any other alternatives?

  5. Pingback: Weekly Links - 05/04/2013 | DB NewsFeed

  6. Another great video Brent! The best explanation of MAXDOP I have come across from any source. CXPACKET waits accounts for over 60% of our current waits, never touched the MAXDOP and Cost Threshold for Parallelism settings due to the conflicting information out there. Thanks for explaining.

  7. Hi Brent , Thanks for your sharing your tech, could you add a video download link? Sorry, I unable to visit the website “www.youtube” in my country. sigh

    Best regards
    Ning

  8. Hi Brent,
    Great vid as always – so you’re saying if we have virtualized SQL Servers that ‘separate’ data/log/tempd onto their own logical drives (in our case, vmdk), that it actually doesn’t matter performance-wise because those vmdks are shared by other servers and it becomes random writes and therefore the idea of sequential writes for log files improving performance is negated? …whew.

    Thx.

  9. Hi Brent. As always thanks for passing out your knowledge.
    I’m on a company that for every change we can/have to make have to be approved by a team (not dba’s) but management. Then it takes 2 weeks to get something approved or not.
    Changes like. the log file is bigger than data files. DBCC Check not being perform in months. System database in the c drive. Tempdb only with one file, on 5 databases that are more than 250 GB each.
    Rebuilding indexes on a daily basis. Shrinkind DB. Users with direct access to the tables and not funtions or even views. Users with admin rights. Now i wonder why the senior DBA left the company. LOL
    Thanks Brent.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php