Blog Archives

SQL Tip: Identifying Column store index potential candidate tables in your databases for major query performance improvements

Column Store indexes appeared in SQL Server 2012 and by 2016 and beyond have progressed much further allowing their usage with fewer restrictions than before and this new storage mechanism for indexes and data provides significant gains for performance especially

Tagged with: ,
Posted in Column Store Indexes, SQL Server, sql server performance, Uncategorized

How to get GDPR compliant with Microsoft

When the EU’s GDPR (General Data Protection Regulation) is introduced next year, the bar for data privacy protection in the UK will be raised. For thousands of businesses GDPR highlights daunting issues of compliance. Organisations that fail to comply with

Tagged with: , ,
Posted in gdpr, security, SQL Server, SQL Server Administration, Uncategorized

SQL Tip: Memory limit too low. Instance failing to start

Quick note. SQL startup parameters set mistakenly by someone to be too low and instance is failing to start. Page/memory allocation errors in startup log of various types… Add startup parameter ‘-f’ and start the instance and change the memory

Tagged with: , ,
Posted in SQL Server, SQL Server Administration, SQL Tips, Uncategorized

SQL Central Management Server – Definition, Benefits and setup

Central management servers store a list of instances of SQL Server that is organized into one or more central management server groups. Actions that are taken by using a central management server group act on all servers in the server group. This includes connecting to servers by using Object Explorer and executing Transact-SQL statements and Policy-Based Management policies on multiple servers at the same time

Tagged with: ,
Posted in SQL Server, SQL Server Administration, Uncategorized

SQL Tip – Comma delimited list from a table column

If you want to extract query result of a column to a variable as Comma separated values list use the following example and modify accordingly. declare @Dblist varchar (max) set @Dblist = ” select @Dblist = case when @Dblist =

Tagged with:
Posted in sql, SQL Server, tsql

Microsoft SQL Server Performance Dashboard Reports – 1 – Intro & Default Install

Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.
CPU bottlenecks (and what queries are consuming the most CPU)
IO bottlenecks (and what queries are performing the most IO)
Index recommendations generated by the query optimizer (missing indexes)
Blocking
Latch contention

Tagged with: , , , , ,
Posted in reporting, sql, SQL Server, sql server performance

SQL Tip: Changing Database names along with file names (logical and physical)

Sometimes you need to rename a databases. Although it seems pretty easy to rename it on the surface. However for long term maintainability it is crucial that the underlying logical files and physical files also reflect the changes. Without the

Tagged with:
Posted in security, sql, SQL Server, tsql

SQL Tip: Read + Write (or any) role on databases, all existing and new ones

Trying to avoid the double hop issue which configuring linked servers, I opted for local users. After setting them up I asked the developer who requested this as to which databases he wanted to be read across the servers /

Tagged with:
Posted in SQL Server, tsql, Uncategorized

SQL Tip: How to find SQL Server installation date and time

Getting right down to business… heres the script SELECT create_date FROM sys.server_principals WHERE sid = 0x010100000000000512000000 And for Eval versions — Evaluation Version Expire Date SELECTcreate_date ASInstallationDate, DATEADD(DD, 180, create_date) AS’Expiry Date’ FROMsys.server_principals WHEREsid = 0x010100000000000512000000 GO Reference: Pinal Dave (http://blog.sqlauthority.com)

Tagged with:
Posted in sql, SQL Server, tsql, Uncategorized

SQL Server 2016 SP1 – The game changing release… seriously

SQL Server 2016 SP1, In short with this release Microsoft has enabled many features in Standard, Web and even Express editions which were only previously available in Enterprise.

Tagged with:
Posted in Uncategorized