Database Administration

01 June 2016

3 Built-In Technologies for Centralizing SQL Server Administration

DBAs can be more effective in managing their workload by centralizing their procedures. There are several features of SQL server that can be harnessed to this end: PowerShell is only part of the solution: there is also Central Management Server, Master /Target Agent and the Remote Server Administration Tools. It's time to work out your objectives and pick the most suitable technologies to meet them. … Read more
01 June 2016

5 Monitoring Queries for SQL Server

Every DBA squirrels away favourite queries for monitoring SQL Server. Nowadays many of these are too complex to keep in your head. Dennes describes how he uses T-SQL queries for solving problems, whether it involves fixing the problems of missing indexes, preventing unrestrained autogrowth, avoiding index fragmentation, checking whether jobs have failed or avoiding memory stress conditions. … Read more
18 April 2016

SQL Server Security Audit Basics

SQL Server Server Audit has grown in functionality over the years but it can be tricky to maintain and use because it lacks centralization and analysis tools. It can do a fast and lightweight audit of many different activities including DML and DDL at both Instance and Database Levels - even the work of the DBAs. How do you check logins and permissions? How do you script an enterprise-wide audit solution? How can you hope to analyse the log data you get? Feodor gets you started.… Read more
13 April 2016

The SQL Server 2016 Query Store: Analyzing Query Store Performance

There are some obvious advantages to having the Query Store, but what is the performance impact that it is likely to have on a busy OLTP database server? It is early days, of course and until we get more experience we have to rely on Microsoft's estimate of a performance impact of 3-5% on average. However, this will depend on a number of factors such as usage an the way it is configured. Enrico explores some of these factors in order to give a clearer picture of what you should expect.… Read more
16 March 2016

The SQL Server 2016 Query Store: Forcing Execution Plans using the Query Store

The SQL Server 2016 Query Store can give you valuable performance insights by providing several new ways of troubleshooting queries, studying their plans, exploring their context settings, and checking their performance metrics. However, it can also directly affect the performance of queries by forcing Execution Plans for specific queries.… Read more
07 March 2016

The PoSh DBA: Assigning Data to Variables Via PowerShell Common Parameters

Sometimes, it is the small improvements in a language that can make a real difference. PowerShell is able to introduce extra common parameters that can be used by any Cmdlet or advanced function. When -PipelineVariable was introduced in V4, we all wondered what it could be used for. Laerte experimented and discovered that it could be a real convenience in every-day scripting with the pipeline.… Read more
11 February 2016

Documenting SQL Server with PowerShell

SQL Server instances are generally poorly-documented. How easily can you tell if something has changed? How easily can you check that there is adequate space for growth? Are you up-to-date with licenses? What errors are happening? Who has accessing the system? Before PowerShell, it was difficult to be on top of all this. Now you can, with the help of Sander's database documenter.… Read more
08 February 2016

Five Online Database Modelling Services

To design, or redesign, a database of any complexity, the Entity-Relationship modelling tool becomes essential. The specialized tools that have dominated the industry for a long while are expensive and are installed on a workstation. Now that browser technology has progressed so rapidly, the online database modelling tools have become viable and are starting to attract the attention of database designers. Are they good enough to use now? Robert Sheldon finds out.… Read more
26 January 2016

The SQL Server 2016 Query Store: Accessing Query Store Information Using DMVs

The SQL Server 2016 Query Store provides several new ways of troubleshooting queries, studying their plans, exploring their context settings, and checking their performance metrics. In using the Query Store to ensure that performance is as good as possible, it isn't long before it becomes important to be familiar with the DMVs that are associated with the query store, and using them in custom queries.… Read more
06 January 2016

The SQL Server 2016 Query Store: Built-in Reporting

One of the most important features of the SQL Server 2016's new Query Store is the reporting. With these features, it is now possible to get a wealth of information on how your query workload is performing, either aggregated for the entire query workload or for a single query. With this information, you can see the effects of 'forcing' an execution plan for specific queries and get feedback of the consequences.… Read more
04 December 2015

The PoSh DBA – Using the Basic PowerShell Paradigms

PowerShell is like any computer language: you must understand the paradigms, the constructs, and the way it is designed to work to get the most value from it. It is no good just translating 'sausage-string' procedural algorithms. To demonstrate how PowerShell should be used as its' creators intended, Laerte Junior shows the difference between PowerShell problem-solving with, and without, PowerShell paradigms.… Read more
16 November 2015

Doing a SQL Server Healthcheck via PowerShell

PowerShell is an ideal tool for doing health checks of a collection of SQL Server instances, and there are several examples around, but few acknowledge the fact that individual DBAs have their own priorities for tests, and need something easily changed to suit circumstances. Omid's health check allows tests to be SQL or PowerShell and requires only adding, altering or deleting files in directories. … Read more
16 November 2015

The SQL Server 2016 Query Store: Overview and Architecture

SQL Server's Query Store, introduced in SQL Server 2016, helps to troubleshoot query performance by capturing a range of information about query usage, CPU, memory consumption, I/O and execution time, and retaining every Execution Plan for analysis. Much of this information is available through queries. It looks set to be the most significant enhancement of SQL Server 2016.… Read more
02 November 2015

SQL Server Heaps, and Their Fragmentation

In SQL Server, heaps are rightly treated with suspicion. Although there are rare cases where they perform well, they are likely to be the cause of poor performance. If a table is likely to have a large number of changes, then it can become fragmented due to way that space is allocated and forward pointers used. How does one detect this problem? Is it significant? How does one deal with it, if necessary?… Read more

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up