tag:blogger.com,1999:blog-72141447647249221712024-03-19T01:43:37.024-07:00SQL VibesSQL Server has always been my first love since I chose a career as a DBA some six years ago.Through this blog I intent to share my daily encounters with issues at work place and also to stay connected with my DBA community in general. Thank you for passing by, please share your comments. -Roshan JoeRoshan Joe Josephhttp://www.blogger.com/profile/05696059904672328124noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-7214144764724922171.post-37681744860590026722012-03-02T22:58:00.008-08:002012-08-06T04:19:51.572-07:00On Primary key and Clustered indexes<div dir="ltr" style="text-align: left;" trbidi="on">
There are so many articles around the net about this topic. Great DBAs have written a lot on this already. Still I see a number of myths regarding clustered index going around among newbies. In fact some idea held by a good friend-colleague made me write this entry.<br />
<br />
Following are the myths many people hold<br />
<br />
<strong>Myth #1) Clustered indexes are always primary key or vice versa</strong>It is true that when we create a primary key on a table by default a clustered index is created. But definitely we can opt for a non clustered index if we have a reason to do so. <br />
<br />
Eg. We create a table as<br />
<span style="color: blue;"><br />CREATE TABLE ClusterMythBuster<br />(<br />PkColumn INT NOT NULL,<br />Column1 VARCHAR(10),<br />Column2 VARCHAR(25),<br />EgFiltercol VARCHAR(10),<br /><br />)</span><br />
<br />
Now the table is a heap (Meaning there is no clustered index, and no physical order in which the data is stored in the table)<br />
Now I will create a primary key that is not a clustered index <br />
<br />
<span style="color: blue;"><br />ALTER TABLE ClusterMuthBuster ADD CONSTRAINT Pk_</span>
<span style="color: blue;">ClusterMythBuster</span> <span style="color: blue;"> PRIMARY KEY NONCLUSTERED (PkColumn)<br /> </span><br />
Now let us add a clustered index to this table to a non key column<br />
<br />
<span style="color: blue;"><br />CREATE CLUSTERED INDEX IX_</span>
<span style="color: blue;">ClusterMythBuster</span><span style="color: blue;">_EgFiltercol ON </span>
<span style="color: blue;">ClusterMythBuster</span> <span style="color: blue;"> (EgFiltercol)<br /> </span><br />
Now we have a clustered index that is not a primary key as well as a primary key that is not a clustered index.<br />
<br />
<br />
<strong>Myth#2) Clustered index cannot be created on non unique column.</strong>This is also not true. This already evident from the earlier example, still we can seal it by trying to insert some value into the above table.<br />
<br />
<span style="color: blue;"><br />insert into </span>
<span style="color: blue;">ClusterMythBuster</span> <span style="color: blue;">values (1,'Test1','Test2',1)<br />insert into </span>
<span style="color: blue;">ClusterMythBuster</span> <span style="color: blue;">values (2,'Test1','Test2',1)<br />insert into </span>
<span style="color: blue;">ClusterMythBuster</span> <span style="color: blue;">values (3,'Test1','Test2',1)<br />insert into </span>
<span style="color: blue;">ClusterMythBuster</span> <span style="color: blue;">values (4,'Test1','Test2',2)<br />insert into </span>
<span style="color: blue;">ClusterMythBuster</span> <span style="color: blue;">values (5,'Test1','Test2',2)<br />insert into </span>
<span style="color: blue;">ClusterMythBuster</span> <span style="color: blue;">values (6,'Test1','Test2',1)<br /> </span><br />
See that now since the clustered index is created on EgFiltercol column, the data will be ordered as first all the ones and then the twos.<br />
<br />
Please note: It is always recommended to create a clustered index on a column that is ever increasing, since it will create less fragmentation.<br />
<br />
<br />
<strong>Myth#3 A clustered index cannot be created on a combination of columns</strong>This is also not true since we can create a composite clustered index. <br />
<br />
See the example below <br />
<br />
First we need to drop the existing since we can have only one clustered index per table<br />
<span style="color: blue;"><br />DROP INDEX </span>
<span style="color: blue;">ClusterMythBuster</span> <span style="color: blue;">.IX_</span><span style="color: blue;">ClusterMythBuster</span><span style="color: blue;">_EgFiltercol<br /> </span><br />
<span style="color: blue;"><br />CREATE CLUSTERED INDEX IX_</span><span style="color: blue;">ClusterMythBuster</span><span style="color: blue;">_CompCluster ON </span>
<span style="color: blue;">ClusterMythBuster</span> <span style="color: blue;">(Column1,Column2)<br /> </span><br />
<br />
However, I would like to end by saying that ironically all the above myths are serving one purpose since it is always safe to choose a column that is unique and ever increasing. It is also better if the clustered index is narrow (less number of columns), <br />
But my point is that if you have your own valid reasons to choose so, you can opt otherwise.<br />
<br />
Please see these links<br />
<br />
<a href="http://www.sqlservercentral.com/articles/Indexing/68563/">http://www.sqlservercentral.com/articles/Indexing/68563/ </a><br />
<a href="http://msdn.microsoft.com/en-us/library/ms190639.aspx%3Cbr%20/%3E">http://msdn.microsoft.com/en-us/library/ms190639.aspx</a><br />
<a href="http://msdn.microsoft.com/en-us/library/ms177443.aspx">http://msdn.microsoft.com/en-us/library/ms177443.aspx</a><br />
<a href="http://msdn.microsoft.com/en-us/library/aa933131(v=sql.80).aspx">http://msdn.microsoft.com/en-us/library/aa933131(v=sql.80).aspx</a></div>Roshan Joe Josephhttp://www.blogger.com/profile/05696059904672328124noreply@blogger.com0tag:blogger.com,1999:blog-7214144764724922171.post-84066956061537989802012-03-01T06:26:00.009-08:002012-03-01T07:26:50.730-08:00Auto Growth considerationsAuto growth is the event that occurs when a query needs additional space in the database to get executed <br /><br />SQL Server provides two options if auto growth is enabled;<br /> Auto growth in MB or in percentage.<br /><br />When the current allocated size is filled, an auto growth event is triggered. During this event SQL collects the required space from OS and initializes it.<br /><br />The auto growth will resource intensive, and hence recommended to give auto growth setting in such a way that it should not occur too often, at same time it should not grow very large that the auto growth itself takes much time to complete.<br /><br /><br />You can alter the file size and growth settings in database property window.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhV_oxmFfLNTCcHoYKicRnItyQsIPAiSM7VLoZdtBAvGf1iBV-es7YyvpB84GyG3U_iZve1SH2G6GzmrAGiKOxJSeO0t5zPDR2_fmoxMIV8TGnuXj6NOhTvetNBuarycQYu4ZonxQbenKQ/s1600/Filegrowth.JPG"><img style="float:left; margin:0 10px 10px 0;cursor:pointer; cursor:hand;width: 320px; height: 200px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhV_oxmFfLNTCcHoYKicRnItyQsIPAiSM7VLoZdtBAvGf1iBV-es7YyvpB84GyG3U_iZve1SH2G6GzmrAGiKOxJSeO0t5zPDR2_fmoxMIV8TGnuXj6NOhTvetNBuarycQYu4ZonxQbenKQ/s320/Filegrowth.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5714939439914725186" /></a><br /><br />Alternately you can use the TSQL command to change database size and growth settings<br /><br /><span style="color:steelblue;"><br />ALTER DATABASE <span style="font-style:italic;">databaseName</span><br />MODIFY FILE (NAME=<span style="font-style:italic;">LogicalFilename</span>,FILEGROWTH=<span style="font-style:italic;">X</span>MB)<br /></span><br /><br />To get the logical name and other informations you can use the following command. <br /> <br /><span style="color:steelblue;"><br />USE DatabaseName<br />SP_HELPFILE<br /></span><br /><br />or<br /><br /><span style="color:steelblue;"><br />SP_HELPDB DatabaseName<br /></span><br />Now the question is how can we find out what is the optimum size for auto growth?<br />The answer is you need to first monitor your database growth and decide on this.<br /><br />If you are using SQL 2005 and abaove and if you have default trace enabled, you can use following query to find this.<br /><br /><br /><br /><span style="color:steelblue;"><br />if<br />(select convert(int,value_in_use) from sys.configurations where name = 'default trace enabled' ) = 1 <br />begin<br />declare<br />@curr_tracefilename varchar(500) ; <br />declare<br />@base_tracefilename varchar(500) ; <br />declare<br />@indx int ;<br />select<br />@curr_tracefilename = path from sys.traces where is_default = 1 ; <br />set<br />@curr_tracefilename = reverse(@curr_tracefilename);<br />select<br />@indx = patindex('%\%', @curr_tracefilename) ;<br />set<br />@curr_tracefilename = reverse(@curr_tracefilename) ;<br />set<br />@base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ; <br />select<br />(dense_rank() over (order by StartTime desc))%2 as l1, <br />convert(int, EventClass) as EventClass, DatabaseName, <br />Filename, (Duration/1000) as Duration, StartTime,<br />EndTime<br />, (IntegerData*8.0/1024) as ChangeInSize <br />from ::fn_trace_gettable( @base_tracefilename, default ) <br />where<br />EventClass >= 92 and EventClass <= 95 and ServerName = @@servername --and DatabaseName = db_name() <br />order<br />by StartTime desc ; <br />end<br />else <br />select<br />-1 as l1, 0 as EventClass, 0 DatabaseName, 0 as Filename, 0 as Duration, 0 as StartTime, 0 as EndTime,0 as ChangeInSize <br /></span><br /><br /><br /><br />To determine space utilized inside the data files for each databases use<br /><br /><br /><br /><br /><span style="color:steelblue;"><br />Create Table ##DB_usage<br />(<br />Database_name varchar(255),<br />Logical_name varchar(255),<br />PhysicalLocation Varchar(1000),<br />size float,<br />Used float)<br /><br />exec sp_msforeachdb <br />'use ?; insert into ##DB_usage<br />select ''?'', name,physical_name,size*8/1024,FILEPROPERTY(name,''spaceused'')*8/1024 from sys.database_files'<br /><br /><br />select * from ##DB_usage<br />drop table ##DB_usage<br /></span><br /><br />For more details see.<br /><a href="http://support.microsoft.com/kb/315512/EN-US/"></a>Roshan Joe Josephhttp://www.blogger.com/profile/05696059904672328124noreply@blogger.com0tag:blogger.com,1999:blog-7214144764724922171.post-77228423616931683722011-07-05T06:24:00.000-07:002012-03-01T00:45:43.494-08:00Beware of NolockThere is a common misconception among the developers regarding the use of nolock inside their query. In a recent meeting with development team, they told me they have included nolock so that it will not lock the table on read. I explained to them, in default isolation level, select query will create only a shared lock on the resource. So what will happen when you put nolock hint in your query? <br />- It will retrive the data ignoring the locks held by updates.<br /><br />This can be demonstrated by a simple example.<br /><br />In sql management studio, open a query window and run the following code.<br /><br /><em><br />create Table Fruits<br />(<br />FruitName varchar(25),<br />Quantity int<br />)<br /><br />Go<br /><br />insert into Fruits values ('Apple',100)<br />Go<br />insert into Fruits values ('Orange',50)<br />Go<br />insert into Fruits values ('Strawberry',500)<br />Go<br /><br />Begin tran<br /><br />Update Fruits set Quantity =75 where FruitName='Orange'</em> <br /><br />See that after updating the quantity for orange, you have not committed the transaction.<br />Now open another query window and run<br /><br /><em>select * from Fruits</em><br /><br />This query will wait indefenitely.<br /><br />Now cancel this query, and try running the same query with nolock hint<br /><em>select * from Fruits(nolock)</em><br /><br />Its completed just like that.<br />Note the quantity of orange will be shown as 75, where as we have not yet decided whether to commit the query in first window.<br />This is called dirty read.<br /><br />To conclude I will quote the app team head <br /><br />"This means I have only two options, either my application be extremely slow, or my data is often inconsistant". <br /><br />Well, we should try other options to tune the code before using nolocks. Above said, there are many situations when a dirty read is not an issue. But it all depends on your business requirementRoshan Joe Josephhttp://www.blogger.com/profile/05696059904672328124noreply@blogger.com1tag:blogger.com,1999:blog-7214144764724922171.post-38319590771162012712011-07-04T08:44:00.000-07:002012-03-01T00:45:43.514-08:00Blocking : Uncommitted Open transactionsOften we come across blocks in SQL server which affects the performance of your production server. There are many information on the net to tune your queries to decrease blocking, or to choose an appropriate isolation level to improve concurrent access. But here I want to mention a rare scenario that sometimes may go unnoticed.<br /><br />When you are analyzing blocks on SQL server, normally you may run a query like <br /><br />Select * from sysprocesses where blocked <>0<br /><br />And you will look at the head blockers.<br /><br />But I would recomment you sometimes check this <br /><br />Select * from sysprocesses where status='sleeping' and open_tran>0<br /><br />If you have a record, you might further want to look at the host,program and the query which was executed recently on the spid.<br /><br />A developer should thoroughly check entire flow of his SP and should ensure that it leaves no uncommited transactions when completed. <br /><br />In some funny situations I have seen people enabling implicit transactions in there query analyzer and simply forget to put commit. By the time we report this issue to him, he must have erased and retyped many other queries in the same window. And will not rember what was the query which left the open transaction.Roshan Joe Josephhttp://www.blogger.com/profile/05696059904672328124noreply@blogger.com0