SQL Server Profiler. SQL Clustering. PCI and Compliance. SQL Server 2014. Maintenance Solutions. Creating a 2 Node SQL Server 2008 Virtual Cluster Part 1. For sometime now, it seems the requirements for the modern DBA have been changing.
Prospective employers not only require the DBA to be an expert in their field (not unreasonable) but also to possess extensive knowledge of the Windows Sever platform and even to the extent of infrastructure technologies such as clustering, DNS, DHCP and Active Directory. Thinking about it more carefully, it seems this is not unreasonable. If you are going to have your DBA administer the SQL Server failover cluster, they should at least understand clustering, DNS and more importantly Active Directory as Microsoft Cluster Service relies upon these technologies.
Storage technologies also play a large part of the SQL Server systems, clustered or not. Hypervisors now play an even bigger part too, with many companies seeking to virtualise whole server estates in some cases. The 2 parts of this guide will cover: Installation and base configuration of the VMWare Server 2.0.x software.
Brent Ozar PLF. Training. Azure SQL. TempDB. SQL Server Extended Events Tips. Move System Databases. Applies To: SQL Server 2016 This topic describes how to move system databases in SQL Server.
Moving system databases may be useful in the following situations: Failure recovery. For example, the database is in suspect mode or has shut down because of a hardware failure.Planned relocation.Relocation for scheduled disk maintenance. The following procedures apply to moving database files within the same instance of SQL Server. The procedures in this topic require the logical name of the database files. To move a system database data or log file as part of a planned relocation or scheduled maintenance operation, follow these steps. For each file to be moved, run the following statement.ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' ) Stop the instance of SQL Server or shut down the system to perform maintenance.
Transaction Log. Using and Creating Mount Points in SQL. Windows Server 2012 R2 - AD FS: Migrate Your AD FS Configuration Database from WID to SQL Server. AD FS can be configured to store its configuration data in the Windows Internal Database (WID) or an instance of SQL Server.
The WID option may be sufficient for many usage scenarios. If you have outgrown the WID option, take the following steps to upgrade to SQL server:
Reporting Services. Managed Service Accounts (MSA) and SQL 2012: Practical Tips - Esoteric. One of the most common dilemmas for SQL Server administrators is whether they should use AD based domain users as the service accounts, or can they leverage the inbuilt accounts like Network Service etc.
If your SQL Server instance is never going to participate in any cross-server contexts (such as availability Groups, Linked Servers, Log Shipping etc.) then you may very well be happy to use one of the inbuilt accounts or even better, in SQL 2012, the special ‘Virtual Account’ feature. While these options relieve you from the overhead of periodically changing and syncing the service account password, they do impose the machine boundary and cause problems when you want to ‘jump’ across instances.
So the classic solution has been to grit one’s teeth and ask for a domain user which will then be configured as the service account. However this brings the hassle of periodic password maintenance, and more importantly that causes downtime. Setup the MSA in Active Directory References. Set up a user account on a SQL server: Management Services. PowerShell content from SQL Server Pro. Although Windows PowerShell has been available to IT professionals going on seven years, there are still many IT pros who are just now deciding to see what the fuss is all about.
Depending on your job, you might find PowerShell an invaluable tool. Microsoft's plan is that PowerShell will be the management tool for all of its servers and platforms. For most IT pros, it's not a matter of if you'll be using PowerShell, only a matter of when. Microsoft SQL Server Version List. How to determine the version and edition of SQL Server and its components.
SQL Server 2014. SQL Server 2008R2. SQL Server 2012. Dynamic Management Views and Functions (Transact-SQL) Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
There are two types of dynamic management views and functions: Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server. Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database. Dynamic management views can be referenced in Transact-SQL statements by using two-part, three-part, or four-part names. All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats; Required Permissions To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission. Dynamic management views and functions have been organized into the following categories.
Indexes – Unused and Duplicates. Indexes aren’t free, and many databases end up with unused indexes.
Every time you make any update to a table you will be updating the clustered index (I assume no heaps, because I hate heaps), and every index that has uses one of the columns that were updated. Inserts and Deletes affect every column and will affect every index, with exceptions for non-typical indexes. These updates cost a lot, to the point that I have several processes that disable certain nonclustered indexes, do all of the work, then rebuild the indexes afterwards. The problem is that you can’t tell very easily if an index is being used or not. SQL Server Running Slow. A user calls to say the app or server is slow today.
Here’s a quick summary of how I get started. It depends on monitoring where you can compare baselines to recent activity, and I include links to all of that code I use. The initial question is too vague. Sys.dm_os_performance_counters Demystified. The dmv sys.dm_os_performance_counters is awesome, if you can understand it.
This is how I make it easy to read and use. Here are the values I watch and why I watch them. My list isn’t going to be perfect and you’re not going to agree with it 100%, and I’m ok with that. First, there is no perfect. Second, if we agree 100% then one of us is just mindlessly following the other which isn’t good. Cache Hit Ratio – I ignore this value, but I still monitor it. Top Tips for Effective SQL Server Database Maintenance. SQL Server Top Tips for Effective Database Maintenance Paul S.
Randal At a Glance: Managing data and transaction log filesEliminating index fragmentationEnsuring accurate, up-to-date statisticsDetecting corrupted database pagesEstablishing an effective backup strategy. Page Life Expectancy isn't what you think... There’s a lot of controversy about the Buffer Manager performance object counter Page Life Expectancy – mostly around people continuing to quote 300 as a threshold for starting to worry about there being a problem (which is just utter nonsense these days).
That’s far too *low* to be the point at which to start worrying if your PLE dips and stays there. Jonathan came up with a better number to use – based on the size of your buffer pool – see the bottom of his post here. But that’s not why I’m writing today: I want to explain why most of the time now Page Life Expectancy is really not giving you useful information. Most new systems today use NUMA, and so the buffer pool is split up and managed per NUMA node, with each NUMA node getting it’s own lazy writer thread, managing it’s own buffer free list, and dealing with node-local memory allocations.
Think of each of these as a mini buffer pool. What does this mean? How to Master SQL Server Index Tuning in One Step. I’m going to tell you a secret. Index tuning is complicated, but it’s something you can become great at. You just need to practice it regularly. Performance Tuning for SQL Server. If you’re building a new SQL Server from scratch, start by checking out Microsoft’s Fast Track Reference Architectures. Page Life Expectancy and Finding Queries using a Specific Index. In the last 48 hours I have seen two different people having the exact same problem so I thought that I would go about blogging some code I’ve had lying around for a while and been meaning to blog that would help them with finding the root cause of their problems.
In both cases, the question started because the person asking it noticed that Page Life Expectancy was below 300, which is based on dated information published by Microsoft before I began working with SQL Server, and consequently is something I’ll discuss a little at the end of this blog post. In the first case I started asking questions about the amount of memory installed, and what was using the data cache in the server, (see Paul’s blog post Performance issues from wasted buffer pool memory).
In the past I have written a number of scripts that can be used to find interesting information in the plan cache of SQL Server and this is just another one of those. Performance issues from wasted buffer pool memory. Back in April I kicked off a survey where I asked you all to send me some information about your buffer pools – how much memory is being used for data file pages and how much of that memory is storing empty space.
I got back data from 1394 servers around the world – thanks! The reason I’m interested in this, and you should be too, is that memory is one of the most important resources that SQL Server uses. If you don’t have enough memory, your workload will suffer because: You’ll be driving more read I/Os because more of the workload can’t fit in the buffer pool.You’ll be driving more write I/Os because the lazywriter will have to be tossing dirty pages from the buffer pool.You may encounter RESOURCE_SEMAPHORE waits because queries can’t get the query execution memory grants that they need.You may cause excessive plan recompilations if the plan cache is too constrained. And a bunch of other things.
Fixing Page Life Expectancy (PLE) How to Identify IO Bottlenecks in MS SQL Server. Problem. Suggested Max Memory Settings for SQL Server 2005/2008. OLTP Blueprint - A Performance Profile of OLTP applications - Microsoft SQL Server Development Customer Advisory Team. We will look at different types of applications, how they use resources, and how one would approach performance tuning each. The performance profile of OLTP differs significantly from a Relational Data Warehouse or Reporting application. Microsoft SQL Server Development Customer Advisory Team. A Sysadmin's Guide to Microsoft SQL Server Memory. Databases. A database in SQL Server is made up of a collection of tables that stores a specific set of structured data.
A table contains a collection of rows, also referred to as records or tuples, and columns, also referred to as attributes. Each column in the table is designed to store a certain type of information, for example, dates, names, dollar amounts, and numbers. 8 Steps to better Transaction Log throughput - Kimberly L. Tripp.
Transaction Log VLFs - too many or too few? - Kimberly L. Tripp. SQL Power Doc - Documentation.