Books online says this:
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.
Oct 21, 2010
Recompilations after index creation
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 says this:
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.
Oct 20, 2010
Today I will be speaking at the user group meeting
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!
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!
Jul 28, 2010
Trigger Happy at SSSLUG
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
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
Jun 20, 2010
Understanding REVOKE
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
Additionally I am adding these users to AdventureWorks. Two database roles also created for this purpose.
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
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'
Jun 18, 2010
Susantha's Presentation on Powershell
As you may know, Susantha Bathige, a fellow DBA at eCollege, gave a great presentation on Windows Powershell 2.0 at the last SQL Server User Group meeting. He had a few hick-ups when he was starting as the laptop couldn't communicate well with the projector. Gogula shared his laptop, but it refused to listen to his commands properly.
On content, presentation style and confidence level Susantha made an impression. I recommend it to all DBAs, developers and IT pro members.
For those who couldn't participate can view the sample code and presentation at here. The pictures taken were available at here (If you are wondering what I was doing when he was presenting, I was working as his assistant during his presentation. I hope it would have helped Susantha to concentrate on the presentation while I was controlling Gogula's laptop.)
Congratulations Susantha. It is very difficult to say that it was your first presentation. Keep it up.
On content, presentation style and confidence level Susantha made an impression. I recommend it to all DBAs, developers and IT pro members.
For those who couldn't participate can view the sample code and presentation at here. The pictures taken were available at here (If you are wondering what I was doing when he was presenting, I was working as his assistant during his presentation. I hope it would have helped Susantha to concentrate on the presentation while I was controlling Gogula's laptop.)
Congratulations Susantha. It is very difficult to say that it was your first presentation. Keep it up.
Jun 13, 2010
"She" is coming. .. I am excited. Are you?
For a change I am writing some thing different this time. This is not a technical article. As you can below, I have no tags to it.
What is her name? I don't know yet. But I know she is coming and I am excited about it.
If you are wondering who she is, I can tell you this: Even though I haven't seen her, I am sure she will be in her best, sexiest appearance when she appears. You are going to wait anxiously to see her coming and working in your office. You may want to work closely with her, see her internal organs and have complete control over her. Initially your office management will be scared / hesitated to take her in, but there is a high possibility she will make her way in. When she comes she will come with much “hosanna” from some, while some others will grumble about her. Those who grumble will find fault with the time she has taken to respond to the calls they have made, the way she is working and the time she takes to deliver results, and even how her internal organs are made. Interestingly only a few will grumble about her sexy appearance as everyone knows that there is no point in talking about it.
Even if she failed to join your firm, you will try to have some quality time with her. You may use your office and after office hours to gather information about her, listen from people who know her already and even make appointments to work with her. Sometimes you will work with her but mostly you will sit and watch when she is doing the hard work for you. Even though she may work very closely with you, she will remain a mystery for some time. Gradually you will start learning some secrets about her. You may be looking for a day to say at least this: “I know many things including some secrets about her”.
I am not sure about you, but I bet many like to have illegal relationship with her, as starting a legal relationship costs a lot these days, you know. Even though her parents will try to avoid these illegal relationships and she too will resist illegal relationships some may know how to get hold of her.
Initially no one will know her actual name, and the actual date she is going to join your firm. People will keep on guessing. Many will refer her with her pet name until they are used to the actual name.
Excited? Now I share the first secret I know about her: Her pet name is Denali
I need to caution you on this: As it happen often in other cases, when time goes, you may lose the excitement and even start looking for a new one.
Now it is time to share the next secret: Denali is the code name for the next version of SQL Server, Microsoft’s flagship database management system. Most probably you will see her coming to your place by next year. So better be ready soon.
What is her name? I don't know yet. But I know she is coming and I am excited about it.
If you are wondering who she is, I can tell you this: Even though I haven't seen her, I am sure she will be in her best, sexiest appearance when she appears. You are going to wait anxiously to see her coming and working in your office. You may want to work closely with her, see her internal organs and have complete control over her. Initially your office management will be scared / hesitated to take her in, but there is a high possibility she will make her way in. When she comes she will come with much “hosanna” from some, while some others will grumble about her. Those who grumble will find fault with the time she has taken to respond to the calls they have made, the way she is working and the time she takes to deliver results, and even how her internal organs are made. Interestingly only a few will grumble about her sexy appearance as everyone knows that there is no point in talking about it.
Even if she failed to join your firm, you will try to have some quality time with her. You may use your office and after office hours to gather information about her, listen from people who know her already and even make appointments to work with her. Sometimes you will work with her but mostly you will sit and watch when she is doing the hard work for you. Even though she may work very closely with you, she will remain a mystery for some time. Gradually you will start learning some secrets about her. You may be looking for a day to say at least this: “I know many things including some secrets about her”.
I am not sure about you, but I bet many like to have illegal relationship with her, as starting a legal relationship costs a lot these days, you know. Even though her parents will try to avoid these illegal relationships and she too will resist illegal relationships some may know how to get hold of her.
Initially no one will know her actual name, and the actual date she is going to join your firm. People will keep on guessing. Many will refer her with her pet name until they are used to the actual name.
Excited? Now I share the first secret I know about her: Her pet name is Denali
I need to caution you on this: As it happen often in other cases, when time goes, you may lose the excitement and even start looking for a new one.
Now it is time to share the next secret: Denali is the code name for the next version of SQL Server, Microsoft’s flagship database management system. Most probably you will see her coming to your place by next year. So better be ready soon.
Jun 12, 2010
Constraints on Temp tables
Yesterday, I had an interesting conversation on temp tables:
All started with this question: What is the best method to create constraints on temp tables? I never expected it to give me a chance to learn something new.
As you know Temp tables are created in tempdb. Local temp tables are visible only to the user, and it allows multiple users to have temp tables on their own. That means while user A has a temp table #MyFirstTable another user can have a table (at the same time) at the same name.
Generally all objects created in a database will have an entry in sys.objects. For the table, the entry will not be on the same name. SQL Server will add additional characters to create a unique name. The objects are dropped when the connection is dropped or reset.
But there is a difference when it comes to constraints. When a constraint is created, it created with the exact name specified and it will have an entry in sys.objects table. For example the following code is going to fail if multiple users are going to execute during the same period.
The reason for failure is nothing to do with syntax. When the first user executes it two entries into sys.objects: a row with system defined name for table and another row for name we specified for primary key. When the second user executes the code, it can add the row for table but it can’t add an entry for primary key. So it will fail for second user.
All started with this question: What is the best method to create constraints on temp tables? I never expected it to give me a chance to learn something new.
As you know Temp tables are created in tempdb. Local temp tables are visible only to the user, and it allows multiple users to have temp tables on their own. That means while user A has a temp table #MyFirstTable another user can have a table (at the same time) at the same name.
Generally all objects created in a database will have an entry in sys.objects. For the table, the entry will not be on the same name. SQL Server will add additional characters to create a unique name. The objects are dropped when the connection is dropped or reset.
But there is a difference when it comes to constraints. When a constraint is created, it created with the exact name specified and it will have an entry in sys.objects table. For example the following code is going to fail if multiple users are going to execute during the same period.
CREATE TABLE #MyFirstTable
(
MyFirstTable_PK int,
CONSTRAINT PK_#MyFirstTable PRIMARY KEY CLUSTERED (MyFirstTable_PK)
)
The reason for failure is nothing to do with syntax. When the first user executes it two entries into sys.objects: a row with system defined name for table and another row for name we specified for primary key. When the second user executes the code, it can add the row for table but it can’t add an entry for primary key. So it will fail for second user.
Apr 26, 2010
Cluster Installation
I am writing this after a long 2 months delay. While I do not try to justify the reasons, Some high priority things at office and issues in my computer kept me out of writing.
Last couple of months we were trying to create a clustered environment. Generally, when it comes to clustering, we get into additional cost.
This was my idea. we need two servers clustered (windows) in active-active topology. Then we need install one instance in as active in Server A and passive in Server B. Then similarly, we can install another instance active in server B and passive in Server A. By using this, we create a fail-over cluster while utilizing the passive server for another SQL Server installation.
Initially, our proposal was not well taken. I was told that that is simply not possible in Windows 2008 cluster. I have seen similar configuration in Windows 2003. So I was pushing hard. Finally the hardware vendors tried on their own and created a test server. Yes it worked. Now we are setting up clustered environment, while 100% utilizing the processing power of the server.
The key is to create the shared disks properly. If that is done, you are almost done.
Interesting? If you want to know more, drop me a mail. I'll send the details.
Last couple of months we were trying to create a clustered environment. Generally, when it comes to clustering, we get into additional cost.
This was my idea. we need two servers clustered (windows) in active-active topology. Then we need install one instance in as active in Server A and passive in Server B. Then similarly, we can install another instance active in server B and passive in Server A. By using this, we create a fail-over cluster while utilizing the passive server for another SQL Server installation.
Initially, our proposal was not well taken. I was told that that is simply not possible in Windows 2008 cluster. I have seen similar configuration in Windows 2003. So I was pushing hard. Finally the hardware vendors tried on their own and created a test server. Yes it worked. Now we are setting up clustered environment, while 100% utilizing the processing power of the server.
The key is to create the shared disks properly. If that is done, you are almost done.
Interesting? If you want to know more, drop me a mail. I'll send the details.
Feb 16, 2010
The Other side of Filtered Index
I see many like the idea of filtered index. This cooooooooooool new feature allows an index to be created on a subset of the data. I have seen many presentations praising this feature.
Most of you who attended the session on “The other side of indexes” at the last Tech Ed would have enjoyed the session well. Pinal Dave was giving a very good explanation on when to use indexes and when not to use them. He mentioned on Filtered index and explained how it will benefit certain type of queries.
If you have a large table (by rows) your index will be larger. It adds complexity over the maintenance plans you need to build. Additionally, statistics around larger indexes can get skewed easily. This may lead to sub-optimal execution plans chosen by SQL Server Query engine.
On of the key features on filtered index is the WHERE clause. The condition may not reference the key columns of the index For example, CREATE INDEX IndexName ON TableName (Column1) WHERE Column2 >100 is a valid statement. This allows a high level of logical partitioning of data. You can create an index for column1 where Column2 is mentioned in the definition but not in the storage.
I am inspired by the feature. Wow filtered index can make wonders. It will ease some pain points from DBAs. Suddenly a question came into my mind: Can I create multiple filtered indexes on the entire table by
Feb 15, 2010
New Service packs for SQL Server
The Group Program Manager of SQL Server team finally announced the news last Friday (Saturday Morning SL time): There will be new service packs for SQL Server 2005 and SQL Server 2008 before end of the year.
SQL Server 2005 sp4 is expected to be released by the last quarter of this year, but the mainline support will end by April 12, 2011.
SQL Server 2008 Sp2 is expected to be released by the third quarter:
Here is the link http://blogs.msdn.com/sqlreleaseservices/archive/2010/02/12/sql-server-servicing-plans.aspx
As you all know, service packs are simply the collection of Microsoft's cumulative upgrades (CU) released after the last service pack. However, service packs go through much deeper quality assurance tests than cumulative upgrades. As a reason, many companies do hesitate to install cumulative upgrades to their production and critical installations. However, with this announcement, many many like to install the service pack.
SQL Server 2005 sp4 is expected to be released by the last quarter of this year, but the mainline support will end by April 12, 2011.
SQL Server 2008 Sp2 is expected to be released by the third quarter:
Here is the link http://blogs.msdn.com/sqlreleaseservices/archive/2010/02/12/sql-server-servicing-plans.aspx
As you all know, service packs are simply the collection of Microsoft's cumulative upgrades (CU) released after the last service pack. However, service packs go through much deeper quality assurance tests than cumulative upgrades. As a reason, many companies do hesitate to install cumulative upgrades to their production and critical installations. However, with this announcement, many many like to install the service pack.
Subscribe to:
Posts (Atom)