In the Happy Database movies, SQL Server is benign and friendly, with no scary surprises lurking around the next corner. In these movies People work together. SQL Server articles, scripts, and discussion groups. Six Scary SQL Surprises Simple Talk. The working life of the DBA can be punctuated by surprises, but they arent generally nice surprises. This is especially true if the DBA is not checking and monitoring the databases for obvious things such as database corruption, and disk space. However, the really scary surprises are less obvious and provide fewer warning signs. Brent Ozar gives six scary surprises that can be avoided by the shrewd DBA. In the Happy Database movies, SQL Server is benign and friendly, with no scary surprises lurking around the next corner. SQL Server Management Studio. Version Information. The release number 17. The build number for this release 14. New in this Release. SSMS 17. 3 is the. What version of SQL Server do I have This unofficial build chart lists all of the known Service Packs SP, Cumulative Updates CU, patches, hotfixes and other. Describes how to transfer the logins and the passwords between instances of SQL Server 2005, SQL Server 2008, SQL Server 2012 and SQL Server 2017 on Windows. Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of. Sql Server 2005 Enterprise Edition Free Download Full Version' title='Sql Server 2005 Enterprise Edition Free Download Full Version' />In these movies People work together. No one but the DBA can make changes to production. People tell us in advance when they want to change something. When query performance degrades, were notified about it. Third Party vendors only deploy good changes. Deployment scripts always work perfectly. Back in the real world of SQL Server, not all of these are true all of the time and the result is that we spend long weekends slaving over fixes for things that should never have been broken in the first place, or that we should have known about before they became a big problem. In this article, I uncover six common but scary surprises that often lurk behind the innocent looking green Play signal in SQL Servers Object Explorer. Ill also discuss how, with the right tools, you can uncover some of these potential threats, and make sure that the second that another rears its head, you know about it. At the end, I provide a Further Reading list, where you can find deeper details on each of the specific monsters, should you suspect that one of them has infested your SQL Servers. And remember, often the call is coming from inside the houseThe monster that messes with your indexes. Lets say we have a simple Customers table with two non clustered indexes one on Customer. Name and one on Date. Added. Weve even gone so far as to ensure that the indexes are on the right fields note that IXCustomer. Name really is on the Customer. Name field CREATETABLEdbo. Customers        Customerld. INTIDENTITYNOTNULL,    Customer. Name. VARCHAR5. 0NOTNULL,    Date. Added. DATETIMENOTNULL,    CONSTRAINTPKCustomers. PRIMARYKEYCustomerld      GO  CREATEINDEXIXCustomer. Name. ONdbo. CustomersCustomer. Name  CREATEINDEXIXDate. Added  ONdbo. CustomersDate. Added  INSERTINTOdbo. Customers    Customer. Name,Date. Added    SELECTBrent Ozar,Get. Date    UNIONALL    SELECTJeremiah Peschka,Get. Date All is well until one morning users report a dramatic slowdown in queries against the Customers table. You drill into SSMS and all looks well in terms of the indexes that are in place. You fire off a quick query and examine the execution plan. SELECTCustomer. Name    FROMdbo. Customers      WHERECustomer. NameBrent Ozar Immediately, you see a problem the query is ignoring the IXCustomer. Name index, and youre seeing a clustered index scan. You investigate further via the sys. SELECTname,         indexid,     type,     typedesc,     isdisabled    FROMsys. WHEREobjectidObjectIddbo. Customers Someone or something has disabled both of our non clustered indexes. In such cases, the index schema remains in place and, as we saw, SSMS gives no visual indication that anything is amiss, but the optimizer will ignore these indexes. Once an index is disabled, SQL Server stops tracking its usage, as inserts, updates and deletes will no longer hit that index. If were bulk loading data and need to do it quickly, it will sometimes vastly improve the performance of the data load if we first disable the non clustered indexes, as otherwise SQL Server will have to modify the index for every row inserted. It is often more convenient to just disable indexes, rather than drop them, as it leaves the schema intact and you can just re enable it afterwards rather than recreating from scratch. Once the load is complete, we re enable the indexes, by rebuilding them. Disable  ALTERINDEXIXCustomer. Name. ONTest. DB. Customers. DISABLE  ALTERINDEXIXDate. Added. ONTest. DB. Customers. DISABLE   bulk load data  nable  ALTERINDEXIXCustomer. Name. ONTest. DB. Customers. REBUILD  ALTERINDEXIXDate. Added. ONTest. DB. Customers. REBUILDHowever, for various reasons, the indexes sometimes dont get re enabled. Typical causes include An ETL process disables indexes to do a nightly load but then someone breaks this nightly process so that it doesnt re enable them. A deployment script disables non clustered indexes in order to alter the clustered index, but then fails before it re enables them. In order to prevent such incidents affecting our end users, we need to monitor regularly for any disabled indexes in our databases and then investigate the cause. We may even want to receive an alert as soon as any process disables an index. My spBlitz script http www. In order to find out as soon as anyone disables an index in one of your databases, there are a couple of options. One is to write DDL Triggers to track all changes to the database schema and log them to a table, or set up email notification. SSMS Standard Reports. If you right click on any database in SSMS and choose Reports Standard Reports, you can access a history of schema changes, as reported by the default trace. However, in my experience, its not always reliable when reporting on large database migrations where hundreds of changes go through very quickly and there is no built in alerting. Another option is to use your SQL Server monitoring tool to track any increases in the number of disabled indexes for each database. For example, with Red Gate SQL Monitor, we can define a custom metric as follows SELECT    COUNTAScnt  FROM       sys. WHERE     isdisabled1. We then simply set an alert for any increases in the count. SOL Monitor and Custom Metrics. SQL Monitor allows you to create and alert on custom metrics. You can share these metrics at sqlmonitormetrics. You can search for metrics such as index metrics and youll find many other useful metrics such as tables with no clustered index defined. The cannibal that eats all your memory. I offer you the Worlds Simplest SOL Server diagram End users ask SQL Server for data. SQL Server runs on CPU and it gets that data from memory or gets it from hard drive. Lets say we have 6. GB of memory and a 1. GB table and, in response to a user query, SQL Server needs to scan the table from start from finish. The speed of this scan boils down to how fast SQL Server can get 3. GB of data off the hard drives of course, in reality, were not using all 6. GB of our memory to cache that table SQL Server and Windows need memory for all kinds of purposes. FIFA 14 - Commentary Files Down there. If a brisk walk at 5 mph is equivalent to getting data off disk, then getting data out of memory is walking at the speed at light. Suffice to say that, in an ideal world, SQL Server would store in memory all of the data requested by our end users. As such, it makes sense to Get as much memory as you can afford 2. GB for a typical server is under 4,0. SQL Server Enterprise Edition. Make sure as much of that memory as possible is available to the SQL Server process sqlservr. SQL Server processes such as SSIS, SSAS, SSRS if you dont intend to use them. Make sure SQL Server can use as much of that available memory as possible for its buffer cache i. Unfortunately, there is a cannibal, which will eat all of your available memory, if you let it. Consider a typical server with 6. GB of RAM, running 6.