In this rather short post, I would like to talk about a very handy and easy to use tool (SQL Server 2012 Best Practices Analyzer-SBPA) that provides a lot of information about all the SQL Server 2012 components installed on the system and checks if the configurations are setup according to the recommended best practices.(
read more)
A few weeks back, I received an email from a good colleague of mine that was really puzzled with the execution of a rather simple query.Finally he gave up and draw the conclusion that there was some sort of a bug in SQL Server.I tried this query myself and it did not work.The query was pretty simple and I could not see why it was not working,in the first place.The answer to this “strange” behavior of SQL Server is not strange at all. We write SQL queries using the T-SQL syntax.The way we form a SQL query is not the same way SQL Server logically (internally) executes the query. (
read more)
In this post I will be looking into a new feature in SQL Server 2012. Actually this is a T-SQL enhancement and it is called Query Paging.Often in our applications we need to page/select a number of rows (or a range of rows if you prefer) by specifying a row count offset in the results. I will be introducing OFFSET which specifies the number of rows to skip before starting and FETCH which specifies the number of rows after the OFFSET to return.We will use OFFSET/FETCH NEXT in combination with the ORDER BY clause. (
read more)
In this post I will be looking into recompilation and stored procedures in SQL Server. More specifically I will show you why it happens,when it happens and the tools to identify recompilation of stored procedures.There are many reasons why a stored procedure will recompile. Some are more obvious than others.(
read more)
In this post I would like to demonstrate with a hands-on example how to use the new THROW statement available to us for error handling in SQL Server 2012.
Well, most of you know that we have TRY-CATCH statements for exception handling since SQL Server 2005.I have blogged about error handling and exceptions in this post. (
read more)
In this post I would like to demonstrate with a few examples how to use the new DMOs (Dynamic Management Objects – DMVs & DMFs) that are available to us with the RTM version of SQL Server 2012.Some of them were present in SQL Server 2008 R2 SP1 but I do not know how many people have noticed their existence. (
read more)
In this post I would like to provide you with a hands-on example on how to perform a
Page Restore
using the SSMS in SQL Server 2012.We could perform page restores in SQL
Server 2005/2008 (with T-SQL ) but in SQL Server 2012 we have
SSMS UI support for the
Page Restore operation.The
Page Restore operation is intended for repairing isolated damaged pages. (
read more)
In this post I will explain when the actual caching (the stored procedure is placed in the procedure cache ) takes place.
Many people think that the execution plan of a stored procedure is cached in the procedure cache when the stored procedure is created. That is a mistake.(
read more)
In this post Ι would like to demonstrate with a hands-on example the negative performance implications when using built-in or user defined functions in a WHERE statement of a SQL query. (
read more)
In this rather short post , I would like to talk about a very handy and easy to use tool (SQL Space Map) that makes it very easy to identify space utilisation for a particular database in our SQL Server instance. (
read more)
In this post I would like to talk about various reports ( SQL Server 2012 Performance Dashboard Reports ) that are available to us (through Microsoft Download) and their purpose is to give us an overview of the overall performance of an SQL Server instance.Basically they can help us identify where bottlenecks (CPU,IO) can be found.These reports identify where blocking occurs (waits-latches).You do not have to install SQL Server Reporting Services in order to use these reports.They retrieve all the data they present us through DMVs. (
read more)
I will be writing a new series of posts about the new T-SQL functions that are available to us in SQL Server 2012.
In this rather sort post I will demonstrate with hands-on examples how to use the new string function,CONCAT(). (
read more)
I will be writing a new series of posts about the new T-SQL functions that are available to us in SQL Server 2012.
In this rather sort post I will demonstrate with a hands-on examples how to use 3 new conversion functions in SQL Server 2012, PARSE, TRY_CONVERT and TRY_PARSE.(
read more)
I will be writing a new series of posts about the new T-SQL functions that are available to us in SQL Server 2012.
In this rather sort post I will demonstrate with a hands-on examples how to use 2 new logical functions in SQL Server 2012, CHOOSE and IIF. (
read more)
In this post I will demonstrate how to install SQL Server 2012 in your machine.
I had no intention of writing such a post but I installed SQL Server 2012 Developer edition a few weeks back in my Windows 7 Ultimate SP1 laptop and it worked just fine.Then I thought it would be a good idea to write a post like that since a lot of people install the latest version of SQL Server. (
read more)
In this post I would like to talk about a new security enhancement that was introduced in SQL Server 2012,user defined server roles.As we all know we had (and still have in SQL Server 2012) user defined database roles for database level permissions.In the latest version of SQL Server we can have custom – user defined server roles for server level permissions.Basically for a server-level principal we can grant server-level permissions with a mix of GRANT and DENY permissions. (
read more)
In this post I would like to demonstrate with a hands-on example how to backup and restore the tail of the transaction log.In this post I will also demonstrate how to take full and differential backups and how to restore them. I will also talk about the various SQL Server backup types, recovery models and how these models affect our backup and restore strategy. (
read more)
In this post I would like to demonstrate with a hands-on example that is not always possible to restore a database to any Edition of SQL Server.We assume of course that the version of SQL Server remains the same. In my machine I have various instances of SQL Server running.I have an instance of SQL Server 2008 R2 Developer Edition and an instance of SQL Server 2008 R2 Standard edition.(
read more)
In this post I would like to talk about a common issue that confuses SQL Server DBAs and developers.RecentlyI had to deploy an ASP.Net web site from the development server to the live production server.The database server on the deployment server was SQL Server 2008 R2.The hosting provider could only provide us with a SQL Server 2008 database server.Most of my colleagues thought there was no problem with that.I had a different opinion.Ι know that this is not possible. (
read more)
In this post I would like to about a new feature in SQL Server 2008, namely Policy-Based Management.With this new feature DBAs can enforce policies to an instance of SQL Server or many instances of SQL Server they are responsible for.
Most DBAs have to administer many SQL Server instances.It is not efficient to configure and manage them separately.DBAs try to enforce standards (e.g naming standards) on the instances they manage but it is very difficult to enforce them. (
read more)
In this post I would like to demonstrate with hands-on examples how to use automation (jobs,alerts,operators,notifications) in SQL Server to automate SQL Server administration tasks.
The importance of automating tasks is profound.The following tasks could be automated
- Backing up the database
- Checking data integrity
- Dropping and re-creating indexes
- Transferring data
(read more)
In this email I will demonstrate in detail how to set up Database Mail.I am sure that this feature is well documented elsewhere in the web but I decided to give it a go.
Database Mail is a very useful feature.Both the Maintenance Plan Wizard and Maintenance Plan Designer have the ability to send e-mails to DBAs.
We can use the SQL Server Management Studio to set up Database Mail. (
read more)
In this post I would like to explain with some hands-on examples how to create a partitioned table in SQL Server and then manage the partitions.
I
know there are samples of how to do that in BOL,MSDN and elsewhere but I
thought that it would be a good idea to give it a try with hands-on
examples.
Many people have found useful my style of posting. They feel they can follow the steps and master the technology/topic. (read more)
In this rather short post I would like to explain the concept of collation in SQL Server. This is a concept that stills confuses SQL Server DBAs and developers. I know that is a well documented feature (in books & the web) but I will give it a shot. (
read more)
I have been looking into ASP.Net MVC 4.0 lately and I thought it would be a nice idea to create a small ASP.Net MVC 4.0 application and write a post about it. So this is going to be a rather long post that will show you with a hands-on example how to...(
read more)
