Performance | Vinod Kumar (Blog home)

Posts Tagged ‘Performance’

Contained Database and Windows User Group May 20th, 2013

Vinod Kumar

We discussed at length about what contained databases are, how to configure, steps to achieve the same etc in our previous post (Contained Databases inside SQL Server 2012). In this post let me show two concepts:

Contained Database with Windows Users – most of the times I have seen people show contained database with SQL Authentication. In reality it applies to Windows Users too.
Set a default Schema for group – This is an enhancement inside SQL Server 2012 where we can set an default schema for Windows User groups too.

Create Windows Group

Let us first create the Windows Group and add two users into this group. In our example, I am adding Balmukund and Pinal into this group. This will be done under Computer Management –> Local Users and Groups –> Groups location.

Create Contained Database

The steps are same as described in blog post Contained Databases inside SQL Server 2012.

SP_CONFIGURE ‘show advanced options’, 1

RECONFIGURE

SP_CONFIGURE ‘CONTAINED DATABASE AUTHENTICATION’, 1

RECONFIGURE

CREATE DATABASE cdb1 CONTAINMENT=Partial

The above statements create the database CDB1.

Create Users and Set Default Schema

First step is to create the users and then give permissions to access our database. Change the same appropriately to suit your needs.

USE CDB1

– Create a database user for Windows Group

CREATE USER [MTC-Vinod\CDBUsers]

– Giving Permission to group

ALTER ROLE DB_DATAREADER ADD MEMBER [MTC-Vinod\CDBUsers];

ALTER ROLE DB_DATAWRITER ADD MEMBER [MTC-Vinod\CDBUsers];

Next step will be to create the schema and give permission to the group:

CREATE SCHEMA Users AUTHORIZATION [MTC-Vinod\CDBUsers];

– Set Default schema for a group!

ALTER USER [MTC-Vinod\CDBUsers]

WITH DEFAULT_SCHEMA = Users

Let us create a test table with some value to do a quick testing.

CREATE TABLE Users.testing(c1 int)

INSERT INTO Users.testing VALUES (10)

Testing Contained Database

In our example, I have used a local user account for the demo. We can start SSMS using the “runas” command which will initialize SSMS in Balmukund’s user credentials. The typical command will look as:

In SSMS menu, select Advanced properties and don’t forget to change your default database to CDB1, else you are likely to get an error. Now the Object explorer shows as:

When you are connecting with Container Database user credentials, we can see ONLY this specific database. All other databases are hidden from the user.

Next step will be to query our tables. In the example below you can see we resolved the table “testing” to our default schema of “Users”.

Final Words

The above code confirms the two concepts we wanted to learn – a) Using Windows users and groups for Contained databases and b) Setting default schema for Windows Groups. The second concept is not specific to Contained databases but can be used for normal DB too.

How many of you use contained database today? Are these cool additions with SQL 2012?Do let me know and would love to hear your feedbacks.

2 Comments »

SQL Server 2012: Audit Filtering May 6th, 2013

Vinod Kumar

This is in continuation to blog on SQL Server 2012: User-defined Audit. In this blog post, let us look at the concept of filtering audits. Organizations want to audit based on specific condition – a typical example is, we want to audit all connections that are outside of application zone. This is to make sure that we are cognizant of the activities that happen from external sources. Audits are used to track what happens in our database and not used for stopping us from doing an operation – that must be achieved using GRANTS, REVOKE and DENY.

Coming back to our topic – Audit filtering. Let us filter based on a specific user. For this, let us first query the username we want to filter on.

In our example, we are going to use principal_id of 1 i.e. sa and we will audit anything other than connections made from sa.

Create Audit

First step is to create the Audit. Notice the last two lines – we are going to add the filter condition.

CREATE SERVER AUDIT [Audit-2013-05-001]

TO FILE

( FILEPATH = N’C:\Audit\’

,MAXSIZE = 0 MB

,MAX_ROLLOVER_FILES = 2147483647

,RESERVE_DISK_SPACE = OFF

)

WITH

( QUEUE_DELAY = 1000

,ON_FAILURE = CONTINUE

)

– change the server_principal_id as needed

WHERE ([server_principal_id]<>(1))

The next task is to start our Audit. This can be done via the UI too.

ALTER SERVER AUDIT [Audit-2013-05-001] WITH (STATE = ON)

Create Audit Specification

Previous step of creating Audit shows WHERE we are doing to audit, this step is to specify WHAT we are going to audit.

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-2013-05-001]

FOR SERVER AUDIT [Audit-2013-05-001]

ADD (USER_DEFINED_AUDIT_GROUP)

WITH (STATE = ON)

Just like what we did in SQL Server 2012: User-defined Audit, we are going to use the User defined Audit. Now the Management Studio looks like:

Create Custom Audit

We are going to create two Audit, one using the current Windows Authentication and other using sa.

– Audit write using current Vinod Account

EXEC sys.sp_audit_write 1, 1, N’This is from Vinod’

– Change the connection to SA

– Audit write using current sa Account

EXEC sys.sp_audit_write 1, 1, N’This is from sa’

View Logs

Select the Audit and view the logs.

The output will contain 2 rows, one stating the service started and second is via the Windows Authentication. Since the second custom audit was written using SA account, it doesn’t get written into our audit.

Final words

This was one of the demo’s I did show to folks who attended TechEd India 2013. I am sure this will be interesting addition to Audit arsenal and do let me know if you will be using this technique in your environments.

No Comments »

SQL Server 2012: TSQL Metadata Discovery May 1st, 2013

Vinod Kumar

Prior to SQL Server 2012, when applications needed to retrieve column metadata for the first result set in a batch they used the FMTONLY set option (details BOL MSDN). This mode allows a user to retrieve metadata for result sets without actually executing the batch. This approach can be inconsistent or error prone in its execution and let me give you a simple example below:

SET FMTONLY ON

IF (1 = 2)

SELECT * FROM Person.Person;

ELSE

SELECT * FROM Person.Address;

SET FMTONLY OFF

Can you see the problem here? We get two result set and if the application is using the first result set metadata information then it will yield a wrong result and ultimately there will be errors. Now that we have understood this behavior, let us move to what has been introduced with SQL Server 2012.

WITH RESULT SETS <options>

SQL Server 2012 introduces this new option and extends the EXECUTE command with a new execution option called as “WITH RESULT SETS <options>”. Using this new option, you can now define the metadata for result set that will be returned by a batch, stored procedure or dynamic SQL (if any). The default option is "RESULT SETS UNDEFINED", which indicates that any type of result set may be returned by dynamic SQL or none at all.

A typical usage is to run the below query on AdventureWorks database – uspGetManagerEmployees has one parameter and it helps find all the people that roll-up to the employee.

EXEC [dbo].[uspGetManagerEmployees] 3

Now this gives a resultset with column names like RecursionLevel, OrganizationNode … etc. Now that is of no use if we look at it from the application point of view. We want something more understandable format. Here is how we will rewrite the same query with meaningful column names.

EXEC [dbo].[uspGetManagerEmployees] 3

WITH RESULT SETS

– This helps redefine column metadata to better

– indicate the result set’s business purpose

(

([Reporting Level] int NOT NULL,

[Employee ID of Manager] nvarchar(max) NOT NULL,

[Manager First Name] nvarchar(50) NOT NULL,

[Manager Last Name] nvarchar(50) NOT NULL,

[ID of Employee] int NOT NULL,

[Employee First Name] nvarchar(50) NOT NULL,

[Employee Last Name] nvarchar(50) NOT NULL)

);

The neat trick I saw is, we can also pre-define our result set as table data type and use that as possible result set column metadata using AS TYPE <table type> option. That was really cool and something interesting to know.

Datatype Conversions

If data type (metadata) for a pertinent column does not match between what is returned by query and what is defined by "WITH RESULT SETS <>" option, an attempt is made to convert the data returned by query to what is defined by the execute option implicitly. If the implicit conversion is not possible, an error is raised.

Below are a typical example of implicit conversion(Query 1) and an Error (Query 2).

– Query 1

EXECUTE (N’SELECT 007 as output’)

WITH RESULT SETS

(

([VALUE] money)

);

– Query 2 – Conversion ERROR

EXECUTE (N’SELECT N”BigData” as output’)

WITH RESULT SETS

(

([VALUE] float)

);

The output is:

sp_describe_first_result_set

When we are on this topic, thought it would be worth to mention about a system stored procedure “sp_describe_first_result_set” and dynamic management view “sys.dm_exec_describe_first_result_set”. The system stored procedure and dynamic management view return the metadata for the first possible result set of the Transact-SQL batch. They return an empty result set if the batch returns no results. They will raise an error if the Database Engine cannot determine the metadata for the first query that will be executed by performing a static analysis.

Below is a typical use of identifying the metadata for 4 columns (object_id, name, type, type_desc) from the sys.objects system table.

EXECUTE sp_describe_first_result_set

N’SELECT object_id, name, type, type_desc

FROM sys.objects’, null, 0

We can perform the same operation on user defined tables too on the database. This is similar to the first example we saw with FMTONLY.

Though these are the two most used functions, there is one more function to know – sp_describe_undeclared_parameters. This stored procedure returns a result set that contains metadata about undeclared parameters in a TSQL batch. A typical example would look like:

EXECUTE sp_describe_undeclared_parameters

@tsql = N’SELECT object_id, name, type, type_desc

FROM sys.objects

WHERE object_id = @id OR NAME = @name’,

@params = N’@id int’

This function considers each parameter that is used in the @tsql batch, but was not declared in @params. It returns one row for each such parameter, with the deduced type information for that parameter. So in our example above, @id is already defined, so result set has one row describing suggested data type of @name parameter.

Final Words

Some final thoughts:

The new Metadata discovery APIs only return metadata for the "first" result set of the batch, unlike the FMTONLY option which returns result set metadata for all possible queries within the batch.
When you execute a batch/dynamic SQL with "WITH RESULT SETS <>" execute option and if the number or type of result sets returned by batch/dynamic SQL do not match the defined option, we will receive an error.

Hope this was an interesting learning and do let me know if you are using the same already in your environment. Would love to know the scenario’s you are using the same.

2 Comments »

Sliding Window based Aggregation–SQL Server 2012 April 19th, 2013

Vinod Kumar

As part of performance testing at MTC, I get a chance to meet a number of customers from the financial domain. A lot of code I see in applications is around creating custom functions for some simple requirements. One of the most sought after requirement is the need for sliding window operation. Did you know we have this function already with SQL Server 2012? When I talk with customers, a lot of them find it interesting and are mostly unaware of this enhancement made. In this blog post I will introduce this with pointers to more read. Having said that, you can also check some other functions which will mimic Access based functions introduced with SQL Server 2012.

Check the Sliding Window implemented using OVER Clause in MSDN.

Here is a typical example for sliding window implementation.

SELECT SalesDate, Sales,

AVG(Sales) OVER (

ORDER BY SalesDate

ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING) as [SlidingAverage]

FROM tbl_Aggregate

In this above example is a case of running average over a sliding window of rows of 10 previous (see PRECEDING keyword), current and 10 next rows (see FOLLOWING keyword).

We can also use the analytics functions introduced with SQL Server 2012 along with the above syntax.

To show how powerful queries can be made, below is a classic example. Let us create a simple budget table with values for allocation across the past 3 years.

CREATE TABLE tbl_Budget

(YearOfBudget INT, GroupName NVARCHAR(10), Budget DECIMAL)

INSERT INTO tbl_Budget VALUES

(2010, ‘Product’, 50000.00),

(2010, ‘Support’, 30000.00),

(2010, ‘Sales’, 40000.00),

(2011, ‘Product’, 60000.00),

(2011, ‘Support’, 40000.00),

(2011, ‘Sales’, 50000.00),

(2012, ‘Product’, 65000.00),

(2012, ‘Support’, 45000.00),

(2012, ‘Sales’, 55000.00)

Now that we have the data in place let us write a query which will show us side-by-side the current budget, previous budget and next years budget for a comparison perspective. This can be used for reporting type queries. We will be using two other analytic functions LAG and LEAD in addition to OVER clause.

SELECT YearOfBudget,

GroupName,

Budget,

– Next Years’s budget

LEAD(Budget, 1, 0) OVER (PARTITION BY GroupName ORDER BY YearOfBudget ) as NextBudget,

–Previous Years’s budget

LAG(Budget, 1, 0) OVER (PARTITION BY GroupName ORDER BY YearOfBudget ) as PreviousBudget

FROM tbl_Budget

The output needs to be seen in 3 section (Product, Sales, Support) for our dataset. The below figure shows the same.

Hope you got an idea of such functions introduced with this version. So how have you used these functions in your environments? Would surely love to hear from you.

Interesting with the above query is, the Execution plan has a new operator added. It is called Window Spool. Window spool operator uses actual spool table to keep sliding window’s rows. It can use either in-memory optimized or tempdb based regular spool tables.

I remember writing about spills over Pinal’s Blog earlier and it is an important consideration. Always remember, optimizing TempDB is an important aspect and these analytic functions add-up to the overall experience. If spooling does occur to disk, you can view it via xEvent profiling too with SQL Server 2012. The event to add is: window_spool_ondisk_warning – Occurs when a Window Spool operator uses on-disk work table. I did write about using the new Extended Events UI in SQL 2012 for Monitoring high CPU over this blog.

Some fineprints as we sign off that you might find useful are:

If PARTITION BY clause is not specified in OVER clause, entire source table is treated as single partition.
If ORDER BY is not specified, ROWS/RANGE clause cannot be present and computation of each row will include entire partition.
If ORDER BY clause is specified, but ROWS/RANGE clause is NOT specified, the default behavior is to have "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" to calculate current row aggregate value.
For an index to be used by Window spool operator, all the columns in PARTITION BY and ORDER BY clause should be specified in composite index.
If the query contains multiple different OVER clauses, only execution strategy in SQL Server 2012 is stacked operation. This means that we will sort for first OVER clause, execute aggregate, sort again for second OVER clause, and execute aggregate again. In the above example, you will see two Window Spool functions.
ROWS clause is always faster than RANGE clause. If ORDER BY column is unique, then it is better to use ROWS over RANGE clause.

Thanks for reading this far. Keep exploring and making the best use of the power that TSQL functions introduce in every release.

No Comments »

SQL Server Quiz: Understanding behavior April 4th, 2013

Vinod Kumar

I wanted to write a simple post and yet make it interesting for everyone. So here is a neat little trivia and wondering how many want to take a shot at this. Why do I get different results for below queries. What could be the reason?

SELECT 20.0 /-2.0 / 5.0 * 3.0

– -16.6666666666666

SELECT 20.0 /(-2.0)/ 5.0 * 3.0

– -6.0000000000

Though the question is simple it is quite intriguing how the engine works behind the scene. So let the ideas flow and any guesses how we can get the same result?

PS: Awesome to see some complete solutions expressed via comments. Thanks to those who took time to write. Understanding the behavior is important and hope we learnt something new here.

12 Comments »

SQL Server 2012: User-defined Audit April 2nd, 2013

Vinod Kumar

Coming back from TechEd 2013, I have loads to share. I have done a number of sessions and I am glad I could meet all of you at the event. This blog gets inspired from what was discussed during the event. One of the lesser known features introduced with SQL Server 2012 is the ability to have User-defined Audits inside SQL Server.

Let me start by creating the normal steps of creation of Audits. Click Security –> Audits –> Right Click “New Audit”.

Here we are configuring the “WHERE” part of Audit. Once clicking the OK, make sure to right click and Enable the Audit.

The next section involves the “WHAT” are we going to audit. Click Security –> Server Audit Specification –> Right Click “New Server Audit Specification …”.

Give it a Name and select the Audit we created in the previous step. Under Audit Action Type, select “USER_DEFINED_AUDIT_GROUP”. This is the new Action type introduced with SQL Server 2012. Click OK to complete the step. Donot forget to Right Click and Enable this Server Audit Specification.

Now that we have created both where we are going to audit and what we are going to audit. The next step is to create the audit.

SQL Server 2012 introduces the new stored proc “sys.sp_audit_write” and this invokes a note on our Audit.

EXEC sys.sp_audit_write 1, 1, N’1 – This is additional Information’
GO

Above is the command for your reference. The second parameter can be used for success or failure. 0=Failure while 1=Success – feel free to use it accordingly.

Once the above statements are fired, we can view the same on our Audit file. Select Security –> Audit –> UserDefinedAudit –> Right Click “View Audit Logs”. We will see 3 records. One to start the service and the two based on our commands.

In this log we can see the command statement, Succeeded as False, and User Defined Information contains the text we wrote. So this is complete in all sense.

Finally, I during the demo I also showed how we can read the Audit files located in a location using TSQL command sys.fn_get_audit_file command. All the UI details are also available via this TSQL too.

Hope you found this interesting and do tell me if you will be using the same in your environments.

5 Comments »

TechEd India 2013: SQL Server and Architecture March 13th, 2013

Vinod Kumar

This time of the year that I get heads-down to bring some cool demo for TechEd and the exploration never stops. This year TechEd India 2013 happens in two cities – Bangalore and Pune. This time next week the Bangalore event would have got over and I am sure we will be in exhausted state to relive the past two days and staring at Pune to execute the same. There is always extra care, attention and preparation that goes behind for every TechEd. This year is no different. I am also excited because this will mark my 10th Year of TechEd on stage. The memories of doing this in 2003 is still fresh and the excitement never dies every single year.

The twitter hashtag to follow for this year TechEd is: #TechEdIn.

What is special about TechEd?

If you ask me there are a number of reasons to be at TechEd personally. My drivers for you will be:

It is a place to socialize and at the same time feel alone. If you attend the tech sessions, you might feel there is so much more to learn and this sometimes alienates people in a good way. Get motivated and get to see what the future of technology is.
Two days of pure learning and no work is something we will never get :). I know it is not a nice thing to say but I couldn’t resist from mentioning. Let me reiterate, it is not vacation time but learning time.
It is a hub for networking. This is one of the key and most important reason to attend. Meet peers, speakers from Redmond, local speakers and many more.
With close to 6+ tracks, HOLs – the choice of sessions are too many. It is always fun !!!
It is also a place to make business contacts, find mentors, make new friends and all these will help you to make your future.

SQL Server – Security Session

For the past two years I have talked around Security in SQL Server. Each year I try to bring some unique perspective and what I find useful from a security point of view. This year it will be new additions to Security with SQL Server 2012. You will get to see:

What has changed in Security
How to use User Defined Roles and Default schema
How to keep DBA out of database (access)
Enhancements to Auditing
Audit filtering and Audit resilience
Contained Databases basics
Using Logon Triggers with Contained Databases
SQL Server 2012 – Service account changes
and if time permits few more demo’s are available …

So don’t miss the action. I have infact given away what to expect out of my 1 hour session. Lots of demo and lots of detailing on how to use SQL Server security effectively.

SQL Server – Transaction Logs Session

Going back to the basics is important. So every TechEd there is one topic that I talk about which is about going back to basics. One question that I get back from customers almost every other session is around Transaction Logs functioning. I think it will be great to have a session around that.

What is Transaction Logs? Why do we need them?
Understanding what VLF’s are
Creation of Logs and the basics
Cyclic Logs and how they function
What is written to Logs and how are they written
What happens when Page Split happens with Logs
How does minimal Logging work and what should we know
Finally if time permits, how does recovery work …

This is just a teaser to what is in store for the session. We have many more interesting concepts to discuss and learn during the session – so don’t miss out.

Architecture Track – Evaluating Availability Options

This is going to be an interesting topic. As part of MTC I discuss about scale out to almost every single customer. Most of the times the discussion boils down to when to use what. We have solutions from Log shipping, Mirroring, Clustering, Replication and now with SQL Server 2012 – AlwaysOn. So the session is around:

Why Scale out? Cant I just scale up?
What changes do I need to do in my application?
When to use what technology – Pre SQL 2012
With SQL 2012 – What is AlwaysOn
What are the 12+ different ways to deploy AlwaysOn for my environment?
How do I decide and what will be the RPO and RTO for each?

This is a pure whiteboarding diagram based discussion. You will learn the theory and the possibilities when working with SQL Server 2012.

Conclusion

As I said, if you are in Bangalore or Pune – don’t forget to attend my session. Most importantly don’t forget to pass me a “Hi” after the session. I will be walking around and it will always great to make a new friend.

PS: If you are in Bangalore or Pune – the Usergroups have given some amazing discounts that you don’t want to miss. It is still not late, check out the Bangalore .NET and SQL Usergroup announcements.

1 Comment »

SQL Server: Security Practices and Checklist March 8th, 2013

Vinod Kumar

Now that you did get a chance to see my previous post around Database designs there have been few who have asked more of these posts. In continuation to that concept, this blog post will concentrate on highlighting the SQL Server security practices that I often call out and recommend to customers in our Architecture and design sessions. As always the disclaimer remains the same – these are generic practices / suggestions and might not be the same in your environment and deployment scenarios.

Security is a topic that is always seen as a after thought and I start reiterating to our every single customer how critical and important this is. Most of these are to be done by the database administrator of the environment. It is also left to us to keep track and constantly audit if any of them have been deviated or broken.

SQL Server: Database Design Practices March 6th, 2013

Vinod Kumar

Talking with customers and solving their real world problems is something I love doing. This is my day job and I never get tired of this. As we meet customers almost every single day we also have our own feel good factors of suggestions. I am not calling these as best practices explicitly because these are general guidelines and there can be exception to these in your environment for the data set and project that you are working. So read this blog post keeping them in mind. Don’t use them something written on stone but as a mini-checklist used to meet your needs.

This will be Part I of many more parts to come in the future as I gather my thoughts on various suggestions I give customers. Do feel free to drop me a line if you want something covered here.

UPDATE: Part II: SQL Server: Security Practices and Checklist

* These are my perspectives of various DO’s and DONT’s. These can be different and you might have your very own standards. These are *general* tips and can be overridden based on the application under question.

Database Design Tips

This is a high level summary of a number of tips discussed later in this blog.

Do create tables with a primary key.
Do Not create tables without clustered index (typically we will have clustered primary keys).
Do Not enforce foreign key relationships in the database with code. Always use Referential Integrity and foreign key declarations.
Do Not develop a system by writing any code before the database schema is modeled and approved by design team.
Do Not reverse-engineer the database schema after the system is built.
Do have consistent data types between columns that are in a primary/foreign key relationship.
Do make database objects descriptive.

Physical DB Model

A physical data model graphically represents the implementation of a database. It is constrained both by the logical data model it represents and the features of the underlying DB. The process of translating a logical model into a physical model includes the following tasks:

Do create “legal” DB names for entities and attributes.
Do assign appropriate data types and null-ability for each attribute.
Do define primary keys, foreign keys and indexes.
Do define rules and defaults.
Do de-normalize if necessary for performance (typically in a Datamart or data Wharehouse database).

Design for Performance

As mentioned earlier, know what is the type of application we are designing. Is it an OLTP, OLAP or Reporting application. Based on this, designs can vary by miles.

Do define the Primary Key on a single integer column which is typically implemented with an identity property. This keeps the dependent tables with a narrower row width and therefore, more rows per page which results in fewer scans.
Do define the Primary Key as clustered. This keeps the secondary indexes much smaller.
Do create non-clustered indexes on Foreign Key columns.
Do make sure the tables have statistics. Keeping “Auto Update Statistics” and “Auto Create Statistics” options ON in the database is the default. For read only databases, it may be necessary to manually create statistics.
Do select the correct “Recovery Model” for a database.
Do use 100% Fill Factor on read-only or seldom updated tables.
Do Not over-index, make sure you know which indexes will be used.

Naming Conventions

A database consists of a collection of tables that contain data and other objects, such as views, indexes, stored procedures, and triggers, defined to support activities performed with the data. The data stored in a database is usually related to a particular subject or process.

Database objects should be named clearly and unambiguously. The name should provide as much information as possible about the object. A name is used to uniquely identifies the object. Make sure this naming is consistent wherever the object is used throughout the database. Saving keystrokes should never be used as a criterion in selecting a name.

The following rules should be followed for all database object names:

Do Not abbreviate unless absolutely necessary due to length restrictions. If abbreviations are used, it should remain consistent throughout a project and be documented.
Do Not use special characters when creating database objects.
Do restrict names to alphanumeric characters.
Do make the database objects descriptive.
Do use Pascal Naming by forming names by capitalizing the first letter of each word without embedded spaces (e.g., InvoiceDetail).

Abbreviation Standards

Do avoid using abbreviations.
Do Not use a different letter to start an abbreviation then the first letter of the word being abbreviated (i.e., XFER is not an acceptable abbreviation for Transfer).
Do eliminate vowels first, followed by the least significant consonants of the word.
Double letters can be eliminated unless they are required for clarity.
Do Not use an abbreviation that strongly suggests another word. (Example: the word “parent” could be abbreviated PRNT but this would strongly suggest the word “print.”. A better abbreviation for parent is PARNT.)
Do use consistent abbreviations of similar words. If Charge is CHRG then Change should be CHNG.
Do Not use acronyms if the acronym is not generally known or is subject to multiple interpretations.
Do use abbreviations consistently in the system.
Do Not try to deviate from consistency. I have seen systems mark as _dt for some date field while the datatype is of Varchar. These are great confusions and a recipe for disaster.

I have seen a number of database designs and some of the abbreviations are pathetic and an eye sore. Sometimes when I read them and turn towards the developer – they carry a smile with a look that says: “I didn’t design or name the system :)”.

**** Below are a bunch of naming conventions I have seen. You are free to use them or even ignore them if they don’t fit the bill of your organization / project conventions. ****

Database Names

Database names should use the same convention as described above for database objects: no special characters, alphanumeric characters only and initial capitals for delimiters between words.

Do Not use DB as a suffix for Database name.
Do Not make the database name plural, Example: Use LegalPartner not LegalPartners.
Do use qualifier suffixes for database names like ODS, DataMart, Factory, Staging, Extract and Archive for special type of databases.
Do make the database name mnemonic and/or descriptive.
Do use Pascal Naming by forming names by capitalizing the first letter of each word without embedded spaces or underscores (e.g., LegalPartner).

Table Names

Do Not make the table name plural, Example: Use Organization not Organizations.
Do make the table name mnemonic and/or descriptive.
Do use Pascal Naming by forming names by capitalizing the first letter of each word without embedded spaces or underscores (e.g., BillingDetail).
Do name Associative tables, tables created to resolve a one to many or many to many cardinality, a concatenation of the two referenced table names.
Example: PersonAddress resolves many to many between the Person and Address tables.

Personally I am not a big fan of prefix of tbl to all the tables as it can be easily got from meta data of sys.tables in one shot easily.

The same principle applies to Views. But it is worthwhile to prefix the view based on where it is being used. This can be standard view (vw), Reporting View (rv), ETL view (etl) etc. So feel free to use them appropriately.

Column Names

This can get little complicated and specific to how people design systems. I like a three part name: [Primary][Qualifier][Classifier]. As the names suggests the Primary describes the column a bit more like in EnglishProductName or AlternateProductID. The qualifier can be the descriptive word like in: InventoryInHandQty. The final classifier can give me an idea of what datatype we might be using Qty = Quantity = Integer, ID = Identifier = BigInt, Dt = Data datatype etc.

Do Not use spaces or special characters in column names.
Do use underscores ONLY when making Foreign Key columns that require a Modifier to make the column name more understandable.
Do Not abbreviate the column name.
Do use the table name primary word when creating a primary key.
Do use “ID” as a column name suffix, if it is an INTEGER identifier. If it’s a VARCHAR column it should end with “Code”.

The following attributes can be used where tracking when/who changed a row of data is required.

CreatedDateTime
CreatedBy_AccountID
UpdatedDateTime
UpdatedBy_AccountID
RowStatus_OptionID – (Optional) Used to mark row as Active/InActive.
RowVersion

Other General recommendation

Do Not use spaces or special characters, underscores are the exception for stored procedures.
Do Not name stored procedure with numbers (e.g. spc_myProc666).
Do spell out the whole column name when defining Indexes.
Do Not number the indexes.
Do use the word “All” for an index that covers all columns in the table.
Do use underscores between the column names help readability.
Do name Primary Keys with PK_ followed by the table name, only.
Do name Foreign Keys with FK_ followed by the child table name, then the column name.
Do Not name Foreign Keys with number suffixes, as in FK_Table_Column01.
Do Not use Double Underscores.
Do name Check Constraints with CK_ followed by the table name, then a colum name.

Final Words

I know this is just a subset of what is available in your development checklist. These are just design list that I have made. The next will be add a security list, development list, deployment list, high-availability practices list etc. I think those will follow in the future.

13 Comments »

SQL Server–Plan Freezing February 26th, 2013

Vinod Kumar

This is in continuation to the posts around hints written earlier – SQL Server TABLE HINT and SQL Server FORCESEEK Hint. In a lot of places we did mention about Plan Guides and Plan freezing over those articles and didn’t show what it actually is. The use of Plan freezing is something similar to KEEPFIXED PLAN which we are used too before. But this is even more powerful than you can ever think. The series of posts are not to encourage you to use these techniques extensively in your environments but should be used as a last resort in your code. SQL Server does a more than decent job is choosing the right plan and sometimes we need to guide it in case of upgraded code, old behaviors, third party code that cannot be changed etc.

SQL Server first added the ability to create plan guides in SQL Server 2005. By applying a hint at the server side, a DBA could override the behavior for a specific problematic query. However, with the first release of plan guides it was recommended that you only create a plan guide when it was strictly necessary and keep the number of plan guides to a minimum.

The plan freezing framework in SQL Server 2008 builds on plan guides and is intended to “lock down” plans for all repeated queries against a database or the entire system as opposed to just a handful of queries. If the server handles a well defined, repeated set of parameterized queries such as queries from an OLTP-based application, then plan freezing allows an administrator to pick a time when the system is running well (i.e., they are happy with the query plans that are being used), “freeze” those plans, thereby ensuring that any subsequent compiles/recompiles don’t pick a different plan and introduce unpredictability into the system.

changes to plan guides and plan forcing in SQL 2008:

In previous versions, if a plan guide applied a hint which resulted in a “no plan” error (8722), the error was returned to the client and the query did not run. In SQL Server 2008, if the hint results in a no plan error the error is caught internally and the query is re-optimized without the hints. You can monitor when this occurs by capturing the Plan Guide Unsuccessful trace event or by using the SQL Server:SQL Statistics\Misguided plan executions/sec performance counter.
Management Studio now allows you to script out plan guides
A new sys.fn_validate_plan_guide function is added which allows you to validate a plan guide and see any error(s) that might occur because of the hints that were applied
The new sys.sp_create_plan_guide_from_handle procedure allows you to create a plan guide with a USE PLAN hint on a statement based on the current cached plan. This feature is what makes it possible to “freeze” plans for a whole database or system.

Knowing the fineprints

The hint in a plan guide created by sys.sp_create_plan_guide_from_handle is a USE PLAN hint (MSDN) based on the current Showplan XML for the statement. The Showplan XML output always contains fully qualified object names ([dbname].[schema].[owner]). When attempting to force a plan, the object names must be valid. If you freeze plans in a database, backup that database and then restore it with a different database name (such as on a test server, bring in a clone database, etc) all of these plan guides with the USE PLAN hints become invalid since the database name changed.

Because SQL 2008 no longer fails the query when the hints are invalid, any queries will run with whatever query plan the optimizer would normally create for that statement rather than with the plan specified in the USE PLAN hint. Fn_validate_plan_guide can be used to determine if there are validation errors of this nature (as long as the original database doesn’t also exist on this server—in which case the object does exists and fn_validate_plan_guide succeeds but the plan guide isn’t used because the object isn’t in the correct database context). The only way to fix this is to script out the plan guide, perform a find/replace on the database name and then recreate the plan guide(s).

Plan Freezing at work

In this section we will walk through freezing a plan, modifying the table so that the forced plan is no longer valid, then using the new trace events and fn_validate_plan_guide to view how we could determine why the plan guide is no longer successful.

1. I am doing this on a test environment and to ensure we get consistent results – we are going to clear the procedure cache using the following command. Please DONOT do this on your production environments.

DBCC FREEPROCCACHE

2. Next let us run a Dynamic SQL query that will insert into the cache.

EXEC SP_EXECUTESQL N’SELECT COUNT(*) as c

FROM Sales.SalesOrderHeader h

JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID

WHERE h.OrderDate BETWEEN @P1 AND @P2′,

N’@P1 DATETIME,@P2 DATETIME’, ’20050730′, ’20050731′

3. Let us find the plan handle for the query that was run in the previous step.

SELECT plan_handle FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)

WHERE text LIKE ‘%SalesOrderHeader%’

4. Using the Plan Handle from previous step, let us create a plan guide for the query.

sp_create_plan_guide_from_handle ‘My_Plan_Guide’,

0x06000E00A81E612F7008DA7D04000000010…

5. Let us run the query again now with SHOWPLAN XML enabled.

SET STATISTICS XML ON

EXEC sp_executesql N’SELECT COUNT(*) as c

FROM Sales.SalesOrderHeader h

JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID

WHERE h.OrderDate BETWEEN @P1 AND @P2′,

N’@P1 DATETIME,@P2 DATETIME’, ’20050730′, ’20050731′

SET STATISTICS XML OFF

Click on the XML hyperlink to view the query plan. Note in the Properties pane that it shows the plan guide was used. Also note that the query plan uses an index named: [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]

6. For illustration purposes let us DROP the index used in the query plan. Remember, we are doing this with-in a transaction so that we can rollback the same for learning purposes.

BEGIN TRAN

ALTER TABLE Sales.SalesOrderDetail DROP CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID

7. Using Profiler, let us try to catch the Plan Guide UnSuccessful events. Below is the event from Profiler. Run the same query from Step 5 again to check if it now produces this event. The XML Showplan also verifies that it didn’t use the plan guide we created before.

8. To determine why the plan guide is not used, look up the plan guide ID and run fn_validate_plan_guide with this ID to see any errors are reported. Note that in this case it specifically indicates that the object referred to in the query is not present.

SELECT plan_guide_id FROM sys.plan_guides

WHERE name = N’My_Plan_Guide’;

SELECT * FROM sys.fn_validate_plan_guide(<plan_guide_id>)

It is interesting to see how the error gets reported. The message is as below.

Index ‘AdventureWorks2012.Sales.SalesOrderDetail.PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID’, specified in the USE PLAN hint, does not exist. Specify an existing index, or create an index with the specified name.

9. We can Disable, Enable or even Drop a plan guide using the following commands.

–Disable the plan guide.

EXEC sp_control_plan_guide N’DISABLE’, N’My_Plan_Guide’;

–Enable the plan guide.

EXEC sp_control_plan_guide N’ENABLE’, N’My_Plan_Guide’;

–Drop the plan guide.

EXEC sp_control_plan_guide N’DROP’, N’My_Plan_Guide’;

We can use sp_control_plan_guide to disable ALL planguides for a given database too. Check the MSDN links for more information.

10. Since we started a transaction earlier, let us rollback the same.

ROLLBACK TRANSACTION

Final words

Hope these steps gave you a high level understanding of how plan freezing works inside SQL Server. Maybe in future posts, will also show how to use Plan Guides with a step by step as above. Do tell me if you are using these in your environments and if so would love to know what is the scenario for the same.

Vinod Kumar (Blog home)

Posts Tagged ‘Performance’

Contained Database and Windows User Group May 20th, 2013

SQL Server 2012: Audit Filtering May 6th, 2013

SQL Server 2012: TSQL Metadata Discovery May 1st, 2013

Sliding Window based Aggregation–SQL Server 2012 April 19th, 2013

SQL Server Quiz: Understanding behavior April 4th, 2013

SQL Server 2012: User-defined Audit April 2nd, 2013

TechEd India 2013: SQL Server and Architecture March 13th, 2013

SQL Server: Security Practices and Checklist March 8th, 2013

SQL Server: Database Design Practices March 6th, 2013

Abbreviation Standards

SQL Server–Plan Freezing February 26th, 2013

Subscribe to Blog via Email

Vinod Kumar

Current activities

Calendar View

Best Blog Reads

Web Presence

Meta

Vinod Kumar (Blog home)

Posts Tagged ‘Performance’

Contained Database and Windows User Group May 20th, 2013

SQL Server 2012: Audit Filtering May 6th, 2013

SQL Server 2012: TSQL Metadata Discovery May 1st, 2013

Sliding Window based Aggregation–SQL Server 2012 April 19th, 2013

SQL Server Quiz: Understanding behavior April 4th, 2013

SQL Server 2012: User-defined Audit April 2nd, 2013

TechEd India 2013: SQL Server and Architecture March 13th, 2013

SQL Server: Security Practices and Checklist March 8th, 2013

SQL Server: Database Design Practices March 6th, 2013

Abbreviation Standards

SQL Server–Plan Freezing February 26th, 2013

Subscribe to Blog via Email

Vinod Kumar

Follow me !!!

Current activities

Calendar View

Best Blog Reads

Web Presence

Meta