This week I was installing a new failover cluster at a client site. Boy have there been a lot of changes regarding clustering between Windows Server 2003 and Windows Server 2008. Microsoft has redefined clustering with Windows Server 2008, supposedly making it simpler and easier to implement, but I found it still quite an effort since a lot of the steps have changed. I found this great article on the web that helped me to set everything up correctly so I thought I would share it here:
Have you ever wondered what edition of SQL Server you really needed for your specific project needs but you could never really remember where you can find the proper documentation to help you decide. I was looking on the web today and it took me a while to find it myself so I thought I would share it with you all now. Take a look at the following link and you can see for yourself.
If you are interested in the costs of the various editions check out the following:
Over the weekend I was running into a blocking issue with one of my clients. I was using SQL Server 2008 and I was used to using the current activity pane in 2005 etc. to find the lead blocker. This is now gone in 2008 with the new Activity Monitor. I started to use a lot of the old ways to diagnose using the profiler etc but this is a very tedious process. I found this issue I was working on lastnight and it is resolved now but I did some searching on the web today and I found a really great article by Cherié Warren . Cherie does a great job describing the ways to do it in SQL 2008 so I would advise you all to take a look when you get a chance. She includes two really great scripts and I have included them below.
To gather Lock stats run the following query:
SELECT er.wait_time AS WaitMSQty , er.session_id AS CallingSpId , LEFT(nt_user_name, 30) AS CallingUserName , LEFT(ces.program_name, 40) AS CallingProgramName , er.blocking_session_id AS BlockingSpId , DB_NAME(er.database_id) AS DbName , CAST(csql.text AS varchar(255)) AS CallingSQL , clck.CallingResourceId , clck.CallingResourceType , clck.CallingRequestMode , CAST(bsql.text AS varchar(255)) AS BlockingSQL , blck.BlockingResourceType , blck.BlockingRequestMode FROM master.sys.dm_exec_requests er WITH (NOLOCK) JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK) ON er.session_id = ces.session_id CROSS APPLY fn_get_sql (er.sql_handle) csql JOIN ( -- Retrieve lock information for calling process, return only one record to -- report information at the session level SELECT cl.request_session_id AS CallingSpId , MIN(cl.resource_associated_entity_id) AS CallingResourceId , MIN(LEFT(cl.resource_type, 30)) AS CallingResourceType , MIN(LEFT(cl.request_mode, 30)) AS CallingRequestMode -- (i.e. schema, update, etc.) FROM master.sys.dm_tran_locks cl WITH (nolock) WHERE cl.request_status = 'WAIT' -- Status of the lock request = waiting GROUP BY cl.request_session_id ) AS clck ON er.session_id = clck.CallingSpid JOIN ( -- Retrieve lock information for blocking process -- Only one record will be returned (one possibility, for instance, -- is for multiple row locks to occur) SELECT bl.request_session_id AS BlockingSpId , bl.resource_associated_entity_id AS BlockingResourceId , MIN(LEFT(bl.resource_type, 30)) AS BlockingResourceType , MIN(LEFT(bl.request_mode, 30)) AS BlockingRequestMode FROM master.sys.dm_tran_locks bl WITH (nolock) GROUP BY bl.request_session_id , bl.resource_associated_entity_id ) AS blck ON er.blocking_session_id = blck.BlockingSpId AND clck.CallingResourceId = blck.BlockingResourceId JOIN master.sys.dm_exec_connections ber WITH (NOLOCK) ON er.blocking_session_id = ber.session_id CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql WHERE ces.is_user_process = 1 AND er.wait_time > 0
To review the objects run the following query:
DECLARE @SQL nvarchar(max) , @CallingResourceType varchar(30) , @Objectname sysname , @DBName sysname , @resource_associated_entity_id int -- TODO: Set the variables for the object you wish to look up SET @SQL = N' USE ' + @DbName + N' DECLARE @ObjectId int SELECT @ObjectId = CASE WHEN @CallingResourceType = ''OBJECT'' THEN @resource_associated_entity_id WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'') THEN (SELECT object_id FROM sys.partitions WHERE hobt_id = @resource_associated_entity_id) WHEN @CallingResourceType = ''ALLOCATION_UNIT'' THEN (SELECT CASE WHEN type IN (1, 3) THEN (SELECT object_id FROM sys.partitions WHERE hobt_id = allocation_unit_id) WHEN type = 2 THEN (SELECT object_id FROM sys.partitions WHERE partition_id = allocation_unit_id) ELSE NULL END FROM sys.allocation_units WHERE allocation_unit_id = @resource_associated_entity_id) ELSE NULL END SELECT @ObjectName = OBJECT_NAME(@ObjectId)' EXEC dbo.sp_executeSQL @SQL , N'@CallingResourceType varchar(30) , @resource_associated_entity_id int , @ObjectName sysname OUTPUT' , @resource_associated_entity_id = @resource_associated_entity_id , @CallingResourceType = @CallingResourceType , @ObjectName = @ObjectName OUTPUT
Did you know that with SQL Server 2008 you can now clear the query plan for a specific statement you are testing? This is a wonderful addition for SQL Server 2008 as you do not have to drop everything in cache anymore. You can just drop the query you are working on. Look at the following code below from Books Online (BOL) and try it out yourself to see how it works:
The following example clears a query plan from the plan cache by specifying the query plan handle. To ensure the example query is in the plan cache, the query is first executed. The
text dynamic management views are queried to return the plan handle for the query. The plan handle value from the result set is then inserted into the
DBCC FREEPROCACHE statement to remove only that plan from the plan cache.
USE AdventureWorks; GO SELECT * FROM Person.Address; GO SELECT plan_handle, st.text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE text LIKE N'SELECT * FROM Person.Address%'; GO
Here is the result set.
0x060006001ECA270EC0215D05000000000000000000000000 SELECT * FROM Person.Address;
(1 row(s) affected)
-- Remove the specific plan from the cache. DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000); GO
For this month’s T-SQL Tuesday, we had a guest host Rob Farley and we were asked to write a blog post about relationships:
As always, even given the event’s name the posts are not limited to T-SQL! Any component of, or software product related to SQL Server, is fair game. MDX, SSIS, LINQ to SQL, Entity Data Model, NHibernate, and any other software product that deals with SQL Server data can be featured in your post. Be creative!
As before, any blogger can–and should–feel free to participate in this event. In order to make the event slightly more international, I’m changing the time range from PST-based to UTC-based. So the rules are:
- Your post must go live between 00:00:00 UTC and 23:59:00 UTC on Tuesday, February 9, 2010
- Your post must link back to this one, and it’s recommended that you clearly identify the post as a T-SQL Tuesday post
- You are responsible for ensuring that a trackback or comment appears here so that I can find the posts
For my post in this month’s TSQL2SDAY submission I chose to discuss SQL Server Diagrams. This month’s focus was on Relationships so I figured the Diagramming in SQL Server would be a great topic to discuss. A lot of people are aware of the capability to add relationships through foreign keys etc but I always found it much more tedious then it can be if we just used the Digraming features in SQL Server. This makes things much more easier to apply relationships and to see how all the tables are relating as a whole. It is also a free Entity Relationship modeling tool. Let’s discuss the basics.
1) First if you never used the diagrams before it will quickly ask you to install some needed files. These will create some system tables that are used to hold the diagram information. They used to be called Davinci Tables but I am not sure what they are called any more.
2) Next we just right click on Diagrams and Select Create New Diagram.
3)From here we add the tables we are interested in setting up relationships for and then hit ok.
For my example we have two simple tables one part table and one order table. Review them below:
Next we click on the ID in the Part Table and hold down on the mouse and drag the ID field to the Order table. This will bring up the following screen shot where we select the Primary Key table and the foreign Key table and the field we are using for the relationship like below:
Finally we can see the completed relationship and we can save the diagram.
This is a much easier way of adding relationships to our databases using a GUI tool that makes it a snap. Try it out and see how much easier it is.
Today I was reading over a really great article online. One of the examples as you could guess was based on one of the SQL Server sample databases. I really wanted to play around with it a bit but wouldn’t you know it? I didn’t have the sample dbs installed anywhere. I typically do not install the sample databases anymore as I always think they just take up space and I will never really use them. Isn’t that always the case? Well I went searching on the internet and I found them here on Codeplex and I figured I’d share the link with everyone. You can download the SQL Server sample databases here.