Encounter with Databases

MVP Again!

2012-01-11T06:27:00.000+05:30

Thanks to those who worked hard to get this again for me. How can I forget the friends encouraged me.
When looking back, I need to admit that I can still say that there are many things I can do better and I hope to do it better this time.

SQL Server database professionals needed

2012-01-03T09:35:00.001+05:30

Hi readers,
If you are from Sri Lanka, I need to share this info with you. There are organizations searching for SQL Server database administrators, database developers and similar positions. As of now we do not have a place to share the resume. But if you are interested in similar positions. Please let me know. I can share your info with others. Offers include working abroad.

Happy New year!

2012-01-01T00:00:00.000+05:30

Will The Performance Improve If The Database Size is Smaller?

2011-11-02T07:38:00.000+05:30

Few days back, when I was speaking at SSLUG (SQL Server Sri Lanka User Group) meeting on "managing VLDB" (VLDB stands for Very Large Data Bases) there was a question came on improving the performance of VLDB. There was a participant suggested that by shrinking the database we could improve the performance. I noted it then and answered later, but it could be useful for many if I share in the blog. So I am sharing it here for the benefit of many.

Argument: Large databases may take longer time to execute a query because scan operations may have to read more pages.

Answer: If you have an operation which scans the entire database (generally a maintenance or troubleshooting related operation) yes you may have to scan more pages. However when you run a query against tables, you will touch only the pages allocated to the table and indexes. If the table is clustered, each page will have a pointer to the next page and all read operations happen based on these pointers. The performance is better, if you have read forward only. when you have free pages at the end of the database, it will not be touched at all. But if you have free pages in between, shrink operation moves the pages at the end to the free space available. It will make the physical order different from logical order and forces read backward. This is the major concern for performance.

Argument: Shrink operations reduces the pages occupied by an object

Answer: NO. Please note this point: Shrink operation does not remove the free space available within a page. Shrink operation does not compress the data either. The free space within a page can occur due to many reasons.

If the clustered index was created with a fill factor between 1-99 generally you expect some space within a page when the index is created/rebuilt.

When page split happens due to updates there is some space left in old page.

When a row within a page is deleted their is free space created. (Okay, SQL Server does not initialize every bit of deleted row to 0. But rather the entry is removed from index and row mark within a page. SQL Server treats that area as free space)

Shrink operations work at page level and not at object level or row level. If there is a free page in the middle and last non-free page will be moved there and that last page will be marked as free. Then it changes the pointers in the pages accordingly At the end, the free pages at the end could truncated if the options are set properly. The internal fragmentation (the fragmentation happens within a page due to page splits etc.)

Then what is the mechanism to remove the fragmentation? The answer is to rebuild the clustered index. As the data is ordered in the clustered index key, it can move the data pages according to the clustered key order. This is why rebuilding a clustered index is a resource consuming operation.

SQL Server OLEDB Deprecation

2011-09-14T12:58:00.000+05:30

Few days back I sent a tweet update on this matter using my twitter account @preethi_1965
For those who want to hear more on this, here there are few things:

Microsoft has officially announced that they are moving away from OLEDB.
http://blogs.msdn.com/b/sqlnativeclient/archive/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access.aspx
http://social.technet.microsoft.com/Forums/en/sqldataaccess/thread/e696d0ac-f8e2-4b19-8a08-7a357d3d780f

There could be a couple of reasons behind it.

SNAC OLEDB does not scale upt o the customer expectation
ODBC is industry standard and It supports cross platform. To meet the complex market and technology needs, Microsoft needs to Maintain ODBC anyway.
Maintaining both OLEDB and ODBC at the same time providing new features is a difficult task.
Interestingly, Initially Microsoft pushed for OLEDB as it can give a better performance over ODBC. However, especially in 64 bit platforms, OLEDB didn't takeover. According to insiders, OLEDB takes shorter paths, less code and provides better performance.

With that said, SQL Server "Denali" most probably be the last version OLEDB. As Denali CTP 3 is not yet feature complete, I expect another 6 to 12 months for the RTM to be released. It gives us space of another seven to ten years to change the code which uses OLEDB. Until then we are rightfully expect Microsoft to support OLEDB.

Just a note, OLEDB is shipped in two forms. SNC(SQL Server Native Client) and MSOLAP. Microsoft is deprecating only the SNC OLEDB. OLEDB for other platforms will be supported continually. SSIS, SSRS and SSAS clients need not to be panicked. Especially SSIS is heavily depending on OLEDB currently and until something else is developed and in place for few years, it will be supported.

I saw an interesting article on Impact of the SQL Server OLE DB Deprecation on Analysis Services.
Please have a look.

Rename sa login to improve security

2011-09-08T20:55:00.007+05:30

Whenever I speak about security, I tell that we should minimally use sa account. SQL server by default keep the sa account disabled.
In addition to this, we can restrict sysadmin rights to few people, and give only what is needed.

Recently I found out that you can even rename sa account. It is a better practice so that people will not even know what the sa account is.
Simply go to security and logins and select sa account in object explorer. Right click there and select “rename” option. You replace the sa name with the new name and you are done. All the database user accounts mapped to sa login and sa owned jobs and objects will work without any issues. This is because even though you have renamed the login name you are still using the same SID 0x01

This means, you have an additional security; earlier Brute force mechanism needs to get only the password of sa. (It already know that they have a user named sa and sa has sysadmin rights. ) Now needs to pass three different options. Finding a valid login name; getting the password; the login should sysadmin have access.

Tool Evaluation: uCertify PrepEngine for 70-432: SQL Server Implementation and Maintenance.

2011-09-04T23:16:00.000+05:30

Recently I was asked to evaluate an exam tool. A tool developed for 70-432 exam: MSTS: Implementation and maintenance of SQL Server 2008.

Before talking about the tool, Let me introduce my readers to the tool itself. It is “uCertify PrepEngine” from uCertify.com. The tool helps the user to learn and practice for the said exam before taking the actual exam. I know that they provide the preparation tool for various other exams too.
To use the tool, you have two options: An evaluation version which gives limited usage. If you find it useful, (There is high possibility you may find it useful) you can upgrade it to a licensed edition.

Let me set this clear first: 100% money back guarantee and braindump free.

Ucertify supports 100% money back guarantee. In their own words, if you don’t get certified in the first attempt, fax them your score and we will return your money. As simple as that.
But it does not mean it is another collection of braindump. In fact, it is brain dump free. That means, you can learn the basics and sit for the exam and pass the exam without cheating. You can really be proud of your results.

Doesn’t that sound interesting? Don't you want to download the evaluation version?

The tool is available here:

It has 14 questions on diagnostic test which allows the user to evaluate where he/she stands in terms of the exam and identify the areas which needs special concentration. It also gives changes of five practice tests (each on with 45 questions) and one final test with 65 questions.

In addition to this user is allowed to take adaptive test, even to customize a test based on the time available. This means, if you have only five minutes of spare time, you can take a “miniature exam” during that time.

Questions are categorized to single choice, multiple selection and simulation types similar to the actual exam.

The tool also provides interactive quiz with 126 questions

Questions cover the areas related to the exam. Based on the answers student can find out the areas he/she needs to concentrate.

Answers are supported by detailed explanation, facts and reference material

Users can add their notes, tags to the questions, Can retake the same exam multiple times. With randomize options, it is hard to answer the question correctly by memorizing the answers, unless you really understand the concepts.

To prepare for the exam, it allows many supporting material

385 study notes, 3 general articles, 8 How to articles and 6 study tips
Keeps flash cards. You can create your own card in the form of study notes.
Simple articles on How to perform certain actions on SQL Server
Study notes mainly on “what is “ gives introduction to different topics in SQL Server.

It has the facility to print the questions and answers too.
Some good things:

Features: Give feedback, Take notes and print the notes/ questions etc.
The tool allows the students to time box the exams. That means it prevents the users from being carried away with some questions/areas.
Create a Custom exam based on the time the user has. Even on the last minute, the student can sit for an exam of an area which he/she needs reassurance about his/her competence.
The detailed explanation is something great. It not just gives explanation on why a particular answer is right, but also why the other answers are wrong. Additionally it gives some reference from MSDN too.
It covers the exam content from what Microsoft has published. I am not an exam expert to authorize whether it covers all the details of the exam, But, I feel that training the students to face the real hard questions (even if they necessarily be part of the exam),will increase the confidence level f the students.

Few enhancements I like to have:

Most of the questions cover the basics of the topics and do not concentrate on advanced areas. For example, In SQL Server 2008, few performance optimizations were done over replication and database mirroring. The questions were concentrating on these high availability features, but not on the performance optimizations. Another good thing to learn I like to see is combining multiple high availability features.
In addition to the explanation, some information on further reading (to get more in-depth knowledge on the subject ) would a nice to have feature and increase the rating of the tool.
Almost all the notes are about "what is xxxx" I like to see more on "what suits where " or "how to "

Few negative points (Actually I have only one):

Some facts about the exam are outdated/incomplete. For example, Microsoft has increased the exam fees few weeks back. But still the tool says the older price. Another thing I noticed was it failed to mention that students from non English speaking countries get few additional minutes as Microsoft feels that they need more time to read and understand the questions.

Please note that I am evaluating only for 70-432 exam, my comments are limited to it only.

UNION Vs UNION ALL

2011-08-30T20:07:00.001+05:30

This issue came up during one of the training I conducted recently. There is a popular belief that UNION will ALWAYS perform a unique filter on the data. While it is true that UNION Clause will ALWAYS RETURN unique set of data, filtering operation either sort or distinct select is not guaranteed.

Take this example:

SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount FROM SalesLT.SalesOrderDetail WHERE ProductID = 799
UNION
SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount FROM SalesLT.SalesOrderDetail WHERE ProductID = 989

SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount FROM SalesLT.SalesOrderDetail WHERE ProductID = 799
UNION ALL
SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount FROM SalesLT.SalesOrderDetail WHERE ProductID = 989

The results will be unique. Irrespective of whether you use UNION or UNION ALL, the results are same. More than that, the execution plan shows that there is no sort operation involved.

Is that mean that we do not have to worry about UNION ALL? Will SQL Server use the right choice all the time, Irrespective of whether we use UNON or UNION ALL? NO!

There are cases where UNION ALL and UNION turns out to the same execution plan, it is not guaranteed that the plan will be same. For example, when some complexity is added to the script, SQL Server will not understand the uniqueness and will turn to filter the data.

Consider this script:

SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount FROM SalesLT.SalesOrderDetail WHERE ProductID = 799
UNION
SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount FROM SalesLT.SalesOrderDetail WHERE ProductID = 989
UNION
SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount FROM SalesLT.SalesOrderDetail WHERE ProductID = 868

Just an additional SELECT is added.

The new compiled plan is completely different. More than that, it introduces MERGE JOIN (UNION) which is used to sort and join the data, against the CONCATENATION operation in the previous query.

The bottom line is use UNION ALL whenever possible. It does remove the ambiguity.

Removing unnecessary files using powershell

2011-08-28T16:46:00.000+05:30

Now a days some people (including me) used to save whole heap of files into their hard disks. Word documents, excel sheets, powerpoint presentations, pictures taken, pictures received from others and white papers (some times they are word documents), code files (including SQL), Then you have Acrobat reader files, e-books, video and audio files. Most of them take a lot of space. In addition to it, I also have multiple versions of it.

Managing the files is a problem. Who likes the idea of losing the files due to of hardware corruption? A simple solution is to take backups. External hard disks are are cheap and handy solution for this issue.
I am not sure how this mechanism works for you, but for me, it is a problem. I have downloaded same documents multiple times, and each time it has gone into a different location.I have also taken backup at different stages, and some times I have removed the files from my hard disk and some times, I haven't. Now it turned out to be that I have same files in multiple backup folders and zip files and it is a painful process to identify them one by one and delete unnecessary ones.
So I wrote e Powershell script, Which goes through my all backups and identify the identical files.
I hope this is useful to you.

$path = "l:\complete"
$d = get-childitem $path
$dd = $d | sort-object Name,DirectoryName
$j =0
$totalspace =0
for ($i=0; $i -lt $dd.Length-1; $i++)
{
if (($dd[$i].Name -eq $dd[$i+1].Name) -and ($dd[$i].Length -eq $dd[$i+1].Length))
{

$line = "FC /B /LB1 """ +$dd[$i].FullName + """ """+ $dd[$i+1].FullName+""" >>L:\Comparefiles.txt"
$line
$j++
$totalspace+=$dd[$i].Length
}
}

"FilesFound "+ $j+ "; totalSpace "+ $totalspace

First I compared the files using FC (File Compare utility that comes with Windows) Later with slight modification I removed one file by moving one file to another location. it needs only one change in the script: Change the line calls FC to call MOVE

$line = "MOVE /Y """ +$dd[$i].FullName + """ """+ $dd[$i+1].FullName+""""

The results are copied into a .bat file (text document with the .bat extension) and after removing the line where you may need both files, the /bat file was executed.
You can further improve the code in various ways:
1. You can include the files you need based on parameters and filters,

$d = get-childitem $path -include *.PDF -recurse | where-object {$_.Length -gt 1000000}

2. You can add additional commands for comparison. If you want to find the attributes you want to use, you can try,
$d[0] |select *
When I tried this is the answer I got:

PSPath : Microsoft.PowerShell.Core\FileSystem::L:\Complete\E\Z\Other Useful Docs\SQL2K5 Install Docs\OLD\1_SQL2K5 Troubleshooting guide.doc
PSParentPath : Microsoft.PowerShell.Core\FileSystem::L:\Complete\E\Z\Other Useful Docs\SQL2K5 Install Docs\OLD
PSChildName : 1_SQL2K5 Troubleshooting guide.doc
PSDrive : L
PSProvider : Microsoft.PowerShell.Core\FileSystem
PSIsContainer : False
VersionInfo : File: L:\Complete\E\Z\Other Useful Docs\SQL2K5 Install Docs\OLD\1_SQL2K5 Troubleshooting guide.doc
InternalName:
OriginalFilename:
FileVersion:
FileDescription:
Product:
ProductVersion:
Debug: False
Patched: False
PreRelease: False
PrivateBuild: False
SpecialBuild: False
Language:

BaseName : 1_SQL2K5 Troubleshooting guide
Mode : -a---
Name : 1_SQL2K5 Troubleshooting guide.doc
Length : 44032
DirectoryName : L:\Complete\E\Z\Other Useful Docs\SQL2K5 Install Docs\OLD
Directory : L:\Complete\E\Z\Other Useful Docs\SQL2K5 Install Docs\OLD
IsReadOnly : False
Exists : True
FullName : L:\Complete\E\Z\Other Useful Docs\SQL2K5 Install Docs\OLD\1_SQL2K5 Troubleshooting guide.doc
Extension : .doc
CreationTime : 29/9/09 01:56:28 PM
CreationTimeUtc : 29/9/09 08:26:28 AM
LastAccessTime : 14/10/09 06:55:56 AM
LastAccessTimeUtc : 14/10/09 01:25:56 AM
LastWriteTime : 27/4/07 10:04:00 PM
LastWriteTimeUtc : 27/4/07 04:34:00 PM
Attributes : Archive

Hope this post helps you to manage your files too:

Speaking again at SSSLUG

2011-08-24T09:26:00.000+05:30

I was scheduled to speak at this user group meeting (That's today.) I am ready with the presentation. However, after having a small hickup on some personal issues, I asked Dinesh Asanka to do it. He too was ready with the presentation, but at our office he got something. He presence was needed at office at that time. So, I am back to square one is doing the presentation.
I am speaking on SQL Server 2008 R2 StreamInsight. This session gives an introduction to Stream Insight.
by the way, we are planning to have live tweets of the session. If you are unable to come please watch the tweets Follow SqlSvrUniverse.

Resources on Denali

2011-08-11T06:42:00.001+05:30

onYesterday, Microsoft has announced the release of Product Guide v1 for SQL Server Code Name “Denali” Community Technology Preview 3 (CTP3). This is a single download that organizes Denali related content for easier viewing. It is available for download at http://go.microsoft.com/fwlink/?LinkID=225814.

The SQL Server Code Name “Denali” CTP3 Product Guide includes useful resources and demos that will help IT Professionals better evaluate of CTP3. This includes:

- 14 Product Datasheets

- 8 PowerPoint Presentations

- 5 Technical White Papers

- 13 Hands-On Lab Preview Documents

- 6 Click-Through Demonstrations

- 13 Self-Running Demonstrations

- 26 Links to On-Line References

- 44 Links to On-Line Videos including 26 Presentations from North America TechEd 2011

Microsoft also stated that they have a short windows of opportunity to shape the final release, so itmay be one of the rare opportunities to provide feedback on the features, usability and effectiveness. Microsoft expects the customers, communities and organizations to provide feedback on SQL Server CTP3 release in the next 60 days.

More to come, stay tuned.

Denali CTP3, Juneau CTP3 & SQL Server 2008 R2 sp1 Available

2011-07-14T08:53:00.001+05:30

Let me give a different order:

SQL Server 2008 R2 sp1

Yes, it is finally available. Check out the links below.

You can download the files based on the processor you have. In addition to bug fixes some interesting enhancements too are added to this service pack.

Dynamic Management Views for increased supportability:

sys.dm_exec_query_stats DMV is extended with additional columns to improve supportabilities over troubleshooting long-running queries. New DMVs and XEvents on select performance counters are introduced to monitor OS configurations and resource conditions related to the SQL Server instance.

FORCESEEK for improved querying performance :

Syntax for FORCESEEK index hint has been modified to take optional parameters allowing it to control the access method on the index even further. Using old style syntax for FORCESEEK remains unmodified and works as before. In addition to that, a new query hint, FORCESCAN has been added. It complements the FORCESEEK hint allowing specifying ‘scan’ as the access method to the index. No changes to applications are necessary if you do not plan to use this new functionality.

Data-tier Application Component Framework (DAC Fx) for improved database upgrades:

The new Data-tier Application (DAC) Framework v1.1 and DAC upgrade wizard enable the new in-place upgrade service for database schema management. The new in-place upgrade service will upgrade the schema for an existing database in SQL Azure and the versions of SQL Server supported by DAC. A DAC is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see

Designing and Implementing Data-tier Applications.

Disk space control for PowerPivot:

This update introduces two new configuration settings that let you determine how long cached data stays in the system. In the new Disk Cache section on the PowerPivot configuration page, you can specify how long an inactive database remains in memory before it is unloaded. You can also limit how long a cached file is kept on disk before it is deleted.

As you may aware, SQL Server 2008 R2 is not supported on Windows XP.

On feature packs, some of the cools stuffs are added. The list below is my favourites

Report Builder 3.0 enhancements:

Additional visualizations including maps, spark lines and data bars are introduced. It also comes with report part gallery. It means you can develop report parts and add them to the reports you want.

Microsoft® System CLR Types for SQL Server® 2008 R2

This allows the CLR based data types of SQL Server, geography, geometry and hierarchyid to be installed outside the server. By doing so, a client application can use the same data types.

Microsoft® SQL Server® 2008 R2 Remote Blob Store

I first heard about it as part of Denali. However, I am surprised to see it as part of feature pack. This component allows the data to be stored in an external data store. Microsoft is providing a client side DLL and SQL Server stored procedures

Microsoft® Windows PowerShell Extensions for SQL Server® 2008 R2

How can I hide my joy to see something connected to PowerShell. In fact one of my favourite tools is PowerShell. It allows server administration, irrespective of whether it is Windows, SQL Server or Exchange, through a set of cmdlets. It eases the tasks of administrators. I hear that a couple of new cmdlets and a new provider have beed added. Need to have a look.

Denali CTP 3

Despite some earlier announcements, CTP3 was made available to public on 12th. Denali is the next version of SQL Server. Earlier references said it would be released in the second half of 2011, yes we are already there. However, Now the messages are suggesting that it will be named as SQL Server 2012 indicating the release date to end of this year, or somewhere in next year.

Let me warn you that you way start downloading files of 2.4 GB of size. Do you have the facility to do so? then go for.

Want to download? Try this link:

https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/

I’ll write about the features of Denali on a different post.

Juneau CTP3

It gives a different user interface, to database developers. It allows Visual Studio to be used to create all database objects from table to stored procedures. People who are familiar with “Data Dude” or “Visual Studio for Database Professionals” can consider Juneau as the next generation of that.

There is little more on it: You can do all design in your local machine, and deploy to SQL Server or SQL Azure. you don’t need different tools to find the changes happened, it is included in Juneau. Additionally, it all in one source control..

Try this link:

http://msdn.microsoft.com/en-us/data/hh297027

There are many more internal projects, Apollo, Velocity, Crescent and many more are on the pipeline. The information we receive are exciting. Lets wait to see them too.

Understanding Page Splits

2011-07-13T21:00:00.001+05:30

Note: I thought of writing about page splits. Initially I started writing a blog and it slowly became a huge article. I was having two minds on whether I should write a series, of blogs or go for a big article. Finally, I have made up my mind for the first option and here is the first part of the article. My plan is to write different aspects of page splits in coming days…

In SQL Server data is stored into a page. A data page is 8 kb of size. It means when the rows are inserted they need to be inserted into a page. There is an exception when large blob data is inserted sql server can insert the data separately into another page and keep only the pointer (For more details refer: row overflow). Apart from that, a row should fit into the page and cannot split into multiple pages.

The decision on where the data should be inserted is decided by the clustered key. The data is logically stored in clustered key order. (I’ll discuss later on why the word “logically” is used here. But it is safe to assume the order of the pages is clustered key order) When a row need to be inserted or updated but it cannot fit into the empty space, the content of the row splits into two pages (without breaking the row) and it is called page split.
There are primarily two reasons for it to occur:
· Data inserts not in clustered key order
· Update of variable length columns
Let’s analyse each scenario:
Now let us assume we have a table which has a row size of 1300 bytes. It means a fully occupied page will have 6 rows. Let us assume that we have a table where a particular page has 5 rows having the clustered key values 2,4,6,8, 10 and 12. Now a new row is inserted with the clustered key of 5. It should be inserted into the same page as the value 5 falls between 4 and 6. But the page does not have space and it needs a new page. However, if the only the new row is copied into the new page, the clustered key order cannot be maintained.
Now SQL server will perform the operation of page split. It will create a new page and copy half of the rows into the new page. Now both pages will be half empty, so that the new page will be inserted into the page it needs to be inserted. In this case the first page will have rows 2,4,5 and 6 and the next page will have rows 8, 10 and 12.
The following code illustrates the point:

CREATE TABLE dbo.Note

(

NoteID int NOT NULL CONSTRAINT PK_Note PRIMARY KEY CLUSTERED (NoteID),

NoteText varchar(2000) NOT NULL

)

GO

INSERT INTO dbo.Note VALUES( 2,'Some notes here.')

INSERT INTO dbo.Note VALUES( 4,'Some notes here.')

INSERT INTO dbo.Note VALUES( 6,'Some notes here.')

INSERT INTO dbo.Note VALUES( 8,'Some notes here.')

INSERT INTO dbo.Note VALUES(10,'Some notes here.')

INSERT INTO dbo.Note VALUES(12,'Some notes here.')

As the data is ordered on clustered key (i.e. NoteID) this will not create page split. However, when the data for the next day is inserted it will cause page split.

INSERT INTO dbo.Note VALUES( 5,'New notes here.')

The reason for page split is it cannot hold the all the rows in the page. When a page split happens the row will be divided into two and the second half of the data will be moved into the new page.
There is another reason for page splits. Assume you have a variable length column in the table. Assume it is defined as varchar(2000). Since it is a variable length column, it will not occupy all 2000 bytes. Lets take this example:

CREATE TABLE dbo.Note

(

NoteID int NOT NULL CONSTRAINT PK_Note PRIMARY KEY CLUSTERED (NoteID),

NoteText varchar(2000) NOT NULL

)

GO

INSERT INTO dbo.Note VALUES ( 1,REPLICATE('a', 900))

INSERT INTO dbo.Note VALUES ( 2,REPLICATE('a', 900))

INSERT INTO dbo.Note VALUES ( 3,REPLICATE('a', 900))

INSERT INTO dbo.Note VALUES ( 4,REPLICATE('a', 900))

INSERT INTO dbo.Note VALUES ( 5,REPLICATE('a', 900))

INSERT INTO dbo.Note VALUES ( 6,REPLICATE('a', 900))

INSERT INTO dbo.Note VALUES ( 7,REPLICATE('a', 900))

INSERT INTO dbo.Note VALUES ( 8,REPLICATE('a', 900))

Even though the NoteText is specified to have 2000 bytes, they hold only 900 bytes. Now when a row is updated with a higher length string, it may need space more than what is available.

UPDATE dbo.Note SET NoteText = REPLICATE('c', 2000) WHERE NoteID = 3
Since there is no space to accommodate the additional 1100 bytes, it needs a page split. Again the page is divided where the original page will have only four rows ( NoteID <=4) and rest will be in new page.
There are couple of differences between a new page getting added and page split.

Data growth in the order of clustered key leads to new pages getting added. But it ALWAYS happen at the end of the page. Page split refers to a page getting inserted in the middle of the list
Adding new page starts with the blank page and only updates a pointer in the previous page. When page split occurs half of the rows are getting copied into the new page.

There are there any more reasons for page splits? Lets look it in another blog…

Speaking Again: Wait Statistics

2011-05-15T23:21:00.001+05:30

Yes I am back from Hibernate mode. Due to various reasons I couldn't update my blog for four long months. A couple of my friends were repeatedly reminding me. Now, I am starting again.
This month, I am scheduled to speak about Wait Statistics at SQL Server Sri Lanka User Group (SSSLUG). I gave this topic somewhere in the beginning of this year, By I need to wait till this month to speak about wait statistics.

Wait statistics is a key part in tracking down application and server performance issues. I believe it is very important to Server Administrators, DBAs and Developers

Due to Vesak holidays, the meeting is postponed to next week and I am expecting an official notice soon.
See you all there...

MVP Award

2011-01-02T07:05:00.000+05:30

As some of you may know, Microsoft has sent me a new year gift. I have been awarded with MVP status.
I thank all those recommended and proposed my name for this award. this is really an encouragement to the work and service I do.
I was never satisfied with all the work I have done in the past. I know as a fact that there is more I can do.

SQL Server 2008 Microsoft Certified Master (MCM) Readiness Videos

2010-12-22T23:36:00.003+05:30

Thanks to Brad McGehee. (Linkedin)
He is the one who first mentioned about this link. (or that is where I saw it)
When I went to the site I was able to discover some more content deep and simple.
Do you want to know about the internals, and like to analyze various ways of handling things, this is something great to visit.

http://www.microsoft.com/feeds/TechNet/en-us/How-to-videos/SQL_Server_2008_Microsoft_Certified_Master_%28MCM%29_Readiness_Videos.xml

I am speaking today at SSSLUG on Excution Plans

2010-11-18T10:39:00.000+05:30

Today the sessions include an introduction to Denali and the second part of Execution Plans Explained. Today's session focuses on specific items in the execution plan: Seeks, Scans and Physical Join operators.

Bug? - Multiple drop and add columns can cause error

2010-11-17T22:39:00.000+05:30

This issue was first reported by my friend and colleague at eCollege Tim Sesst. We did a lot of research on this. Dinesh Asanka, Susantha and I really got into this. Initially we thought this as a feature, but it took some time for us to understand the issue. Now we feel that it is a bug.

Before going any further, check this code:

IF OBJECT_ID('TestAlterTable') IS NOT NULL

DROP TABLE TestAlterTable

create table dbo.TestAlterTable

(

Id int identity not null CONSTRAINT PK_TestAlterTable PRIMARY KEY CLUSTERED

,col1 char(8000) not null

,col2 char(49) not null

)

Declare @i int =1

While @i <100

begin

print @i

IF OBJECT_ID('DF_Col2') IS NOT NULL

ALTER TABLE dbo.TestAlterTable DROP CONSTRAINT DF_COl2

Alter table dbo.TestAlterTable Drop Column Col2

Alter table dbo.TestAlterTable add Col2 char(49) NOT NULL CONSTRAINT DF_Col2 DEFAULT('a')

set @i+=1

end

Based on the number of columns and the data types you have, at one point, you may receive an error similar too this:

.Net SqlClient Data Provider: Msg 1701, Level 16, State 1, Line 9
Creating or altering table 'TestAlterTable' failed because the minimum row size would be 8061, including 8 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

The number of times you need to drop and add columns depend totally on number of columns and data types used.
Initially we thought that this was due to cleaning of data of the dropped columns from data pages. Like the ghost entries, (where the row is deleted logically, but the data is still there in the data page) I was even prepared to name this action as ghost columns but, sorry, I was wrong. This happens at the meta data level.

Still we haven;t figured out the reason behind this. But based on our research, it has all the behaviors of a bug. It does not happen at the first time.

Interestingly, if you rebuild the primary key, you can continue on the same path for another fixed number of times.

If anyone knows a good reason to say, why this is not a bug, please let me know.
Thanks to all the guys at eCollege who worked on this.

Index Remained Fragmented EVEN AFTER Index Rebuild Job

2010-11-09T21:12:00.016+05:30

I had an interesting thing to find out this week. An Organization had an issue: Their queries are running extremely slow. They did some research on this. They found that even simple queries which uses index seek too are extremely slow. They copied the data into another database and created the indexes and executed again. They were quite fast. There were no other processes running when this test was carried out.

Through another friend of mine they got my contacts and they called me. I got the execution plans from both databases and they were identical. Then with the help from them, I ran a query using sys.dm_db_index_physical_stats against this index and found that it is fragmented. They rebuilt the index and the query started working well again.

Using the same DMV against other tables, I found out that the indexes are fragmented. They sent me the figures they received using dm_db_index_physical_stats Fragmented means some are 80% - 95% fragmented. To be honest with you, I even saw a few indexes showing 100% fragmentation which I have never seen before. I asked them to rebuild the indexes. Later I explained them how these house keeping jobs could be automated.

After a couple of weeks, they called me again. The indexes are fragmented again. I asked them whether they have added the index maintenance job. They answered back saying they have an index maintenance task as part of the database maintenance job and even made it as the first task of the job, [which runs on Sunday nights] but that has not resolved the issue.

First of all I asked them whether there is any other huge process like ETL job running afterwards. They politly said, that the index maintenance task is just part of the database maintenance task and they don’t do any other tasks in the nights. They do other things, like backing up database and executing few DBCC commands as part of the database maintenance jobs

I was clueless. I just tried another blind shot. I asked another question to run the physical stats on Monday morning to see the results. When they came back with the results, it showed like the rebuild index didn’t make any improvement.

I lost my patience. Is this step enabled? I even logged into their system (with the permission from their management) and checked the job. Yes it is. Then what is going wrong?
While checking I just checked the other jobs too. There I found the issue. The last step of the job was Shrink database. I remembered this basically a major re-organizing event. I moves the data/index pages drastically.

Is this the cause for the issue?

My tests on a different server showed me that shrinking the database could cause fragmentation of indexes. Additionally, I was able to get some blogs too on this subject. The best blog I got was this:

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/13/629059.aspx

One paragraph says this:

“Shrinking a database file moves pages from the end of the allocated range of a database file to somewhere at the front of the file, below the shrink threshold. It pays no attention to any index ordering or potential fragmentation. Hence, shrink operations on data files are excellent ways to introduce significant logical fragmentation.”

When you read this paragraph, if you think this clearly says the issue, you should read the next line:

“I've seen people who's maintenance plans are a nightly rebuild of all indexes, followed by a database shrink - total waste of time!”

I disabled the shrink database task. Even though it took some time to understand the issue, it is a good learning for me.

Red gate labs

2010-11-02T08:37:00.000+05:30

Red gate has opened its lab for public viewing!

It is web site, where the tools developed are available for download. They include
Log Shipping Monitor, SQL Backup to MTF Converter (which converts a red-gate backup to native SQL Server backup and Red Gate Snapper (that allows you to create SQL Compare snapshot files for SQL Server databases).

The lab does include tools for .Net developers. Red Gate Memory Tracker (analyzing application memory usage, including loaded libraries, system heap memory and .NET memory) and Diagnostic Tool (used to collect useful system information) will help serious developers.

These are tiny tools, which i believe Red-gate may include or have included with their products. Now these tools come as free downloads.
I haven't tested all the applications yet, (in fact I haven't tested any of them) but I feel that most of them are really useful.

Try this link
http://labs.red-gate.com/

NO Recompilations when (parameter) values are different

2010-10-22T22:42:00.002+05:30

Please read another post on this subject by Dinesh Priyankara.
http://dinesql.blogspot.com/2010/10/do-we-need-to-recompile-stored.html

This is another topic came in the last user group meeting:
Does SQL Server generate a new plan (in other word recompile) the query if the parameter value is changed?
The actual answer is it depends:
First of all, SQL Server does not go through the recompilation process simply because the parameter is changed.
As I mentioned in the previous blog, there are only certain conditions where an existing execution plan is thrown away and a new plan is used.

Even though in my example, a new plan is created between these two statements, the value is not the cause.

DBCC FREEPROCCACHE

GO

SELECT * FROM Sales.SalesOrderDetail 

WHERE SalesOrderDetailID <= 1

GO

SELECT * FROM Sales.SalesOrderDetail 

WHERE SalesOrderDetailID <= 1000

GO

When I ran the queries and captures the SQL:Recompile event, nothing was captured. Interestingly, ShowPlan XML for Query Compilation too, didn't give any number as the reason for recompilation. For me it looked like SQL Server didn't consider them as same query.
I removed the GO statement in between and tried too. The result was same. (except the column line number showed 1 and 2 instead of 1 always.
The image is attached.

Fortunately, SQL Server provides a few DMVs (dynamic management views) which allows us to check the execution plans

SELECT usecounts, cacheobjtype, objtype, text 

FROM sys.dm_exec_cached_plans 

CROSS APPLY sys.dm_exec_sql_text(plan_handle) 

So I did the test again.
First I cleared the procedure cache by issuing

DBCC FREEPROCCACHE

Then I removed the GO command in between the statement and executed the two select statements in one batch followed by query on DMV. Both statements were combined into a single query in the cached plan.
Then I added the GO statement and then executed again.
I saw an interesting thing. instead of showing one query, it shows two queries. and each has it's own plan. Additionally all statements were categorized as ad-hoc statements. The prepared statement was missing.
That means, parametrization didn't identify both queries as same.

I know that my computer uses simple parametrization. So I went ahead and change the setting to force parameterization.

ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED

This time I found out that the ad-hoc query started using the same execution plan.

Not only that, I also saw a new plan for the prepared statement with parametrized query.

Finally, I was able to learn something new. Even if your parameter values are totally different, SQL Server will not take that as a reason to recompile the query.
I was thinking that it was reusing the plan by looking at the query test of the execution plan. Both queries, not only returned the same text, but also replaced the value 1 and 1000 in @1. I assumed that SQL Server has identified them as parameters.

So, its time to learn something new. Even if the execution plan shows something don't trust it. Use different queries and confirm.
Thank you Dinesh for showing me something to learn.

Recompilations after index creation

2010-10-21T17:29:00.003+05:30

Yesterday I got an interesting question: Does adding an index to a table force the execution plans to be recreated?
Books online says this:

Please note the first, second and fourth bulleted points.

It says either a structure should be changed or index used by the query should be changed. In other words not any change in the index will not force recompile.

Books online also lists at least 11 scenarios where a recompile could occur. They are listed on the same article. As you may know, we can capture recompile event using profiler. SP:Recompile and SQL:StmtRecompile events could be used to capture it. These events have EventSubClass which represents the reason for recompile. These are the reasons.

Schema changed.
Statistics changed.
Deferred compile.
SET option changed.
Temporary table changed.
Remote rowset changed.
FOR BROWSE permission changed.
Query notification environment changed.
Partitioned view changed.
Cursor options changed.
OPTION (RECOMPILE) requested.

As you may have noticed any structural changes to a table force all execution plans for depending tables to be marked as invalid. At the next execution of the code, the execution plan will be recreated. It may even come up with the same execution plan.
Interestingly, event sub class does not show anything connected to indexes. Now the question is does SQL Server consider a change in index as structural change?
So I decided to perform a test on this. This is my plan.

Get a query which uses a clustered index scan due to unavailability of index
Create the index and see whether it forces the execution plan to recompile
Create a completely unrelated index on the same table and see whether it forces recompile of the query.
I need to perform this for ad-hoc queries and stored procedures

While the above tests are performed I monitored SP:Recompile and SQL:StmtRecompile events

USE AdventureWorks;
EXEC sp_helpindex 'sales.SalesOrderHeader'
-- TerritoryID, ContactID columns are not indexed
GO

Now I created the stored procedure named Get_SalesHeader_ByTerritoryID. I will use the same query with the same parameter for all the tests.

Then I opened SQL Profiler and create a new trace event. (For this test, using profiler window to capture the data may be okay, but if you are using a busy system it is recommended to use server side trace.)
I selected the following events and columns:

Showplan XML for Query Compile (TextData, SPID)
SP: Completed (TextData,SPID)
SP:Recompile (TextData, SPID , EventSubClass)
SQL: BatchCompleted (TextData, SPID)
SQL:StmtRecmpile (TextData, SPID,EventSubClass)

Before starting the test, let us understand what these events are
SP: Recompile is fired when either an entire procedure or an individual statement is recompiled. SQL:StmtRecompile is fired when an ad-hoc SQL statement is fired. (EXEC too could be considered as an ad-hoc statement) Showplan XML for Query Compile is fired only when query is compiled and it shows the execution plan)
These events will help me to identify the not only the recompile events, but also the new execution plans.
I wrote the following script to perform the tests.

use AdventureWorks;
exec sp_helpindex 'sales.SalesOrderHeader'
-- TerritoryID, ContactID columns are not indexed
go
USE [AdventureWorks]
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Get_SalesHeader_ByTerritoryID]
(
@TerritoryID int
)
AS
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE TerritoryID = @TerritoryID

GO

DBCC FREEPROCCACHE
GO
SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TerritoryID =5
go
CREATE INDEX IX_SalesOrderHeader_TerritoryID on Sales.SalesOrderHeader(TerritoryID)
go
SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TerritoryID =5
go
DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_TerritoryID
GO

DBCC FREEPROCCACHE
GO
SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TerritoryID =5
go
CREATE INDEX IX_SalesOrderHeader_ContactID on Sales.SalesOrderHeader(ContactID)
go
SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TerritoryID =5
go
DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_ContactID
GO

DBCC FREEPROCCACHE
GO
EXEC Get_SalesHeader_ByTerritoryID @TerritoryID = 5
go
CREATE INDEX IX_SalesOrderHeader_TerritoryID on Sales.SalesOrderHeader(TerritoryID)
go
EXEC Get_SalesHeader_ByTerritoryID @TerritoryID = 5
go
DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_TerritoryID
GO

DBCC FREEPROCCACHE
GO
EXEC Get_SalesHeader_ByTerritoryID @TerritoryID = 5
go
CREATE INDEX IX_SalesOrderHeader_ContactID on Sales.SalesOrderHeader(ContactID)
go
EXEC Get_SalesHeader_ByTerritoryID @TerritoryID = 5
GO
DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_ContactID
GO

Here are the results:

The above results are for ad-hoc queries. The results below show the behavior for stored procedures.

It shows that irrespective of whether it is a stored procedure or ad-hoc query, the results are identical. Not only that, Irrespective of whether the index created is useful for the query or not, the recompile event occurred and a new plan is created. If the new index is found to be useful for the query a new plan is created. Otherwise the same plan is created again.
Another point to note is, the recompile event occurs because SQL Server considers an index creation as part of schema change. (Refer the event sub class value for the changes)
This may be another place for Microsoft to change in books online.

Today I will be speaking at the user group meeting

2010-10-20T07:05:00.001+05:30

As some of you know, SS SLUG (SQL Server Sri Lanka User Group) meets today. Generally we have two topics to discuss.
According to the plan we had earlier, I will be in the panel of the second topic (database design)
But due to certain unforeseen issues, at the last minute, I accepted the challenge to speak at today's user group meeting. That means I will speak on execution plans first and then will be in the panel for the second topic too.

If everything goes well with the plan, In today's presentation, I will talk about reading execution plan, various table and index operations, and the logic behind it. At the end of the session, I expect the users to guess the execution plan for simple queries

See you there!

Trigger Happy at SSSLUG

2010-07-28T00:06:00.014+05:30

Hope you were there at the last user group meeting!

I was talking about triggers. This was a beginner level session.
The code samples and the slide deck are uploaded at SSLUG. you ncan download them from here:
http://sqlserveruniverse.com/v2/SSSLUG/Downloads.aspx

Understanding REVOKE

2010-06-20T10:15:00.004+05:30

This is one of the topics I was planning to write for a long time, but failed due to various reasons (and without valid reasons) So, this may sound too basic for some people; if so, please forgive me. I have seen much confusion over REVOKE statement. Some believe that revoke is a different word for DENY. As explaining the security model is worth a series of articles, in this post, I am going to explain only about REVOKE statement. For this, I am using AdventureWorks database. I created two logins which I am using throughout the post

CREATE LOGIN [Sam] WITH PASSWORD=N'MyPassw0rd!_', DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE LOGIN [Joe] WITH PASSWORD=N'MyPassw0rd!_', DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Additionally I am adding these users to AdventureWorks. Two database roles also created for this purpose.

USE [AdventureWorks]

CREATE USER [Joe] FOR LOGIN [Joe] WITH DEFAULT_SCHEMA=[dbo]

CREATE USER [Sam] FOR LOGIN [Sam] WITH DEFAULT_SCHEMA=[dbo]

CREATE ROLE [Sales] AUTHORIZATION [dbo]

EXEC sp_addrolemember N'Sales', N'Joe'

EXEC sp_addrolemember N'Sales', N'Sam'

CREATE ROLE [Marketing] AUTHORIZATION [dbo]

EXEC  sp_addrolemember  N'Marketing', N'Sam'

To understand revoke, we need to understand that GRANT and DENY are two permissions, which a user or role can have. There are few rules related to these permissions

GRANT and DENY are mutually exclusive permissions. That means, a user or role cannot have both GRANT and DENY permissions working. If both statements are issued against a user/role directly, the latest statement will stand as valid. (However, they could be given indirectly; the next rule explains it further.)

GRANT EXECUTE ON [dbo].[uspLogError] TO [Joe]

DENY EXECUTE ON [dbo].[uspLogError] TO [Joe]

DENY has higher priority over GRANT. That means, when a person gets both GRANT and DENY permissions, DENY will be in effect. (There are exceptions and mechanisms to overcome them. But it does not happen at the same level) For example, use Joe could be a member of sales role. Joe may have GRANT permission to perform select operation over a table where, the sales role may have deny permission over the same table. In this case Joe will not have read permission as DENY has higher priority. Similarly, if Joe has a DENY permission, even if his group has GRANT permission, DENY permission will work against him.

GRANT EXECUTE ON [dbo].[uspLogError] TO [Sales]

The above statement, grants execute permission to sales role. As Joe and Sam are members of Sales role, they should get access to execute the procedure. But Joe has explicit DENY permission and it takes priority over GRANT permission. That makes only Sam having permission to execute the procedure.
REVOKE is not a permission. Rather it is a command which removes the permission the user/role already has. Assume that you execute the next statement.

REVOKE EXECUTE ON [dbo].[uspLogError] TO [Joe]

As the last statement was REVOKE, it will remove the permission given at the previous statement. As Joe was given DENY rights before it will be removed. It does not mean that he has GRANT permission now. The state is no permission is set for Joe on Execution of uspLogError.
Now Joe is also a member of Sales role. So he can execute the procedure in his capacity of member of sales role.
Now consider this example:

GRANT EXECUTE ON [dbo].[uspLogError] TO [Marketing]

The first statement, grants execute permission to Marketing role.

REVOKE EXECUTE ON [dbo].[uspLogError] TO [Sales]

Now the GRANT Permission is removed from sales role. That means, all its members will have permission if they have direct or indirect permission only. This will make Joe losing the execute permission. But Sam will still have permission as he is part of marketing role.

I hope this explains REVOKE statement.