In-Memory storage technology finally make their debut appearance on the SQL Server 2014′s BI stack, with the creation of a proper memory optimized tables and stored procedures, unlike the Columnstore feature which offers a read-only memory optimized solution, that does not work overly well in a true transactional environment.

In this post I hope to dissect the new In-Memory tables feature of SQL Server 2014, providing an overview of how the technology works, how to create in-memory tables, maintain them and any pitfalls to watch out from. Mainly though, I am writing this as a reminder to myself of the latest articles I have been reading about this cool new feature.

Read more →

There are a few ways you could grant a user execution permission on stored procedures, through assigning permissions on different object hierarchies (objects/schema/database) you can control the level of permissions to ensure optimum security and flexibility.

This post will go through how to grant SQL Server execution permissions on individual stored procedure objects within a database, how to grant execution permission on all object within a schema (including functions), and finally how to grant execution permission across the whole database.

Read more →

SQL Server provides Full-Text search capabilities through it’s Full-Text Index, a mature document search tool with neat features like thesaurus and stop-word integration as well as some semantic search and keyword extraction features in SQL Server 2012.

The Full-Text Index is used through 2 (scalar) functions CONTAINS and FREETEXT, and 2 (table-valued) functions CONTAINSTABLE and FREETEXTTABLE. In this post I will be briefly exploring the difference between each of those functions.

Read more →

SQL Server uses two methods to ensure transactional consistency and protects the data that is being accessed, these are Locks and Row-Versioning, these methods ensure that you manage your data concurrency effectively by specifying the level of access other transactions have to the data being processed, the game here is to balance either resources or data integrity against concurrency.

Read more →

Being able to holistically view your SQL Server Job Agent job schedules, how long each job took to run, what other jobs were running at the same time and the status of each job, should be a fundamental part of Job Agent management.

In this post I will be briefly introducing the current SSRS reports integrated into SSMS (or SQL Data Tools) that displays SQL Server Job Agent job information, as well as provide a new report (pictures above) that should give you a better holistic view of activities on your SQL Server Agent, as well as highlight any conflict in job schedules or times were no activity is occurring.

Read more →

There are multiple ways to access SQL Job Agent jobs execution statistics, such as:

• Through SQL Server Management Objects (SMO): which provide a programmatic way of accessing and controlling many SQL Server objects. The Smo.Agent namespace will provide the required objects for accessing job statistics such as the last time each job (or step) ran, the duration the job ran for and the returned status as well as many other relevant information regarding job execution.
• Through undocumented Stored Procedures such as master.dbo.xp_sqlagent_enum_jobs: Although this doesn’t provide a lot of flexibility, particularly if you are executing and consuming the data within the context of T-SQL, rather than programmatically through wrapper classes that simplifies handling stored procedures with multiple output tables. It is important to note that since this stored procedure is undocumented, Microsoft could potentially retire it without prior notice, leaving your code non-functional.
• Through Job Agent System Tables: In my opinion this provides the best and most supported method of access. for the rest of this post I will be discussing the different System Tables provided to access SQL Server Job Agent statistics and execution information.

Detecting and reporting when a SQL Server database data or log file experienced a growth operation can be very useful for many reasons, such as:

• Analyzing and predicting disk space utilization on the server
• Detecting optimum values to setup for database growth
• Detecting any long running transactions that causes growth in log or data files.

In this post I present a simple method for querying information regarding database growth activity within a SQL Server instance, as well as setting up a simple email alert that gets triggered based on a configured growth threshold.

Read more →

Diagnosing CPU performance issues with SQL Server can be a bit tricky, particularly if you have a system that exhibit a CPU spikes pattern throughout the day. You could run some SQL DMVs in order to identify holistically how much CPU time each query is consuming, but that does not give you a real-time way of tracking CPU resource starvation on SQL Server.

Recently I have been asked to devise a system on a few of our servers that are experiencing CPU resource bottleneck issues, the monitoring system needs to identify when SQL Server is suffering from CPU bottlenecks, and report the queries currently running on the server via email alerts, along with performance statistics that can help you diagnose which queries are consuming most of the resources, and why are they doing so.
Read more →

Sometimes one might like to identify a particular SQL Server resource using the Page ID this resource is under, you might notice a Page ID sometimes on the Activity Monitor page under the Wait Resource column, if a certain query is waiting on a particular resource, some DMVs will reference Page IDs as well as the SQL Server log files, I find it handy to be able to see exactly what SQL Server Object falls under this Page ID, in order to diagnose any issues with access to that particular resource.

Read more →

There are many ways to obtain the execution plan of a SQL Server query, each of which serves a particular purpose, for example if you can run a query easily, then the best way to get an execution plan is to simply enable the execution plan (either through SSMS or SETting the correct session option), but if the query takes a long time to run, or you simply cannot run the query any other reason, then you might decide to profile the query when it is naturally running (perhaps on the live environment) through SQL Profiler.

In this post I will go through a simple SQL query to extract the execution plan from SQL’s own plan cache. There are many reasons why this might not be an accurate way to extract a query, but it is certainly helpful in situation were you have good understanding of your DB setup.

Read more →