|
|
Concerning development of bespoke applications and database administration.
-
Hi. Tony and Simon will be away hobnobbing at an MVP do in Seattle and so have asked me to fill in for them at the next London SQL Server User Group meeting. In case you hadn't guessed from the title its on April 17. This month's meeting is being held at Microsoft's Victoria office. A map can be found here In addition to the news round-up, beer and nibbles I am delighted to say that we have two excellent speakers lined up: Christian Bolton - "Will you still want me when I'm 64" - covering all things x64 like Eric Alsop - "the cursors of SQLstein!" - guidance on appropriate application of cursors (Ooo... controversial). Full Agenda is as follows: 5:30 - Registration 6pm - 6.30pm Round Table Discussion
Update on what's been going on and is going on in the SQL Server space.
Bring your SQL problems and ask the audience, bounce ideas - anything related to SQL Server.
6.30pm – 7.30pm Will you still want me, when I'm 64? - Christian Bolton (COEO)
An in-depth look at the move toward 64-bit computing with SQL Server covering where and why it will help, what the caveats are and the most up to date best practices gathered from global SQL Server deployments.
7.30 – 7.50pm Break
7.50pm - 9.00pm The cursors of SQLStein - Eric Alsop (IM Group)
Nothing divides the SQL community like a cursor, so why are they still around? In this session we are going to look at how to use cursors and when they are most applicable To register for the event please head over to sqlserverfaq.com and book your place! Cheers, James
|
-
This one is a bit surprising. Assume you have a cold SQL Server which you have just re-booted. The buffer cache is empty and lots of I/O requests are going to be physical. Boo! Hiss! Down with physical I/O. Well help is at hand (if you are using SQL Server Enterprise Edition). Ramp-up is the process of converting your I/O's from page size 8KB reads to extent level I/O's i.e. 64KB. This isn't the extent the page belongs to per se but just an equivalent size. SQL Server does this to accelerate the warming up process of the server. Essentially rather than reading pages to satisfy your query request it pulls back the surrounding 7 pages to flood the cache with data just in case with the hope that you the user will request one of those pages next. There is over-head in doing this but I don't believe it's much and the benefit of subsequent requests being potentially logical rather than physical I/O's offsets this cost. However, you may see 64KB I/O's in perfmon until this period of ramp up has finished. Thrilling stuff eh. However, the question I know that's on your lips is; How do I know when it's finished ramping up? Well as with most things the answer lies in a DMV. sys.dm_os_sys_info is the puppy. This DMV tells you what the target size for the buffer cache is (bpool_commit_target) and also what the current committed value is (bpool_committed) i.e. how much data is already in cache. Once these two numbers are the same then ramp up ends...till the next time. The scantily clad documentation for this feature can be found in the I/O Basics chapter 2 paper here. Cheers, James P.S. Conchango are recruiting! If you are passionate about Data, SQL Server or Business Intelligence then we'd like to hear from you. Why not contact me and let's have a chat?
|
-
Are you using multiple files? Are you set up for proportional fill? Is it a warehouse? Are you concerned about extent fragmentation? You should be. Extent fragmentation upsets your lovely read-ahead and results in smaller I/O. Let me explain. When SQL Server allocates - it does so on a one extent at a time. If multiple data files are involved in your database then this will be performed in a round-robin style to ensure you spread the data across the files proportionally (assuming you have met the rules for prop fill). Now if you have multiple objects requesting extents then you can quickly see that it is unlikely that one table is going to be built with contiguous extents and is therefore likely to be spread out across the files. In an OLTP environment extent fragmentation is not an issue and can actually be a good thing as it can mean that the load is spread out across your disk subsystem. However, in a data warehouse it is a right pain. The reason being is that the read ahead can only operate against contiguous areas of disk and this fragmentation is almost certainly going to put the mockers on that. More specifically it will impact the size of the read-ahead and so consequently the efficiency of the I/O. So what can you do? Well if you have x64 Enterprise Edition (which if you are doing any warehousing of significance I hope you are) then help is at hand. However, check that out - Enterprise Edition and 64 bit only - niche or what. Nice start for the mini-series though :-). -E (note the case sensitivity -e is the path for the error log) in your startup options changes the allocation from 1 extent i.e. 64kb to 4 extents i.e. 256kb. Now this still isn't the biggest I/O you could have received (more on that later) but it's better than a poke in the eye with a stick coated in something brown and fragrant. There is a support KB article on this which can be found here. N.B. This option isn't currently documented in BOL. However, I have fed this back to the documenters and they have assured me that it will be in the next release of BOL. Cheers, James
|
-
I thought it might be fun to do series of posts on features that are exclusive to enterprise edition but that aren't significant enough in their own right to make it on to the big ticket list. Sometimes people balk at shelling out for SQL Server Enterprise Edition without realising on what they are missing out. So my objective is to bring some focus to these oft forgotten features. Some of these will be known to those of you that came to see my last presentation but I thought I'd document them individually as I came across them. So the first three posts will be I'll link em together as I post them up. If you have any that you'd like to share then please feel free to get in touch and I'll add them to the list. Cheers, James P.S. Conchango are recruiting! If you are passionate about Data, SQL Server or Business Intelligence then we'd like to hear from you. Why not contact me and let's have a chat?
|
-
Whilst at SQLBits I heard a rather shocking statement: Over 80% of the worlds SQL Server instances are still using SQL Server 2000. I say statement because I have had a little trawl round the web and I couldn't find anything to back this number up (or what it included or excluded e.g. MSDE could weigh heavily into this number). However, I think it is fair to say that for one reason or another there is still a large number of users out there chugging along on SQL 2000.
I digress. As I am sure you are now well aware the end is near for SQL Server 2000. April 2008 and its good-bye mainstream support. This did actually get me thinking but on a slightly different track than normal.
I wonder how many of these SQL 2000 instances are using availability features such as clustering and/or are used to support a critical business process?
How many are there because people are too frightened to touch them?
Having a critical system in an unsupported state from the product vendor is not the best availability strategy I am sure you will agree.
However, equally as important is the understanding of that system and what it does and why it is so important to the operation of the business. So I'd say now is the perfect time to reflect on those systems that are falling out of support and ask yourself the following questions:
- What does this system do?
- What business processes does this system support?
- Is it a system that needs a high availability strategy?
- Why aren't we upgrading it?
- Can we afford to be without it?
- If there is an issue with the product what is our coping strategy?
- If I need to deploy this onto new or different hardware how will I do this (make sure you have the media!)?
- How will I buy the licenses for SQL 2000 come May?
If you are using clustering also don't forget that you can cluster SQL Server 2005 standard edition (2 node) and you do not need enterprise edition for this feature.
Note: even if the application itself hasn't changed I am sure the data and the data volumes you push into or through it has changed.
Oh and if you are not sure what you should do then of course conchango are here to help :o)
Cheers, James
P.S. Conchango are recruiting! If you are passionate about Data, SQL Server or Business Intelligence then we'd like to hear from you. Why not contact me and let's have a chat?
|
-
Saturday March 01 2008 was the second SQLBits conference. This is not an ordinary conference - it's a community conference created by some of the UK's leading SQL Server practitioners. Tony, Simon, Allan, Darren, Martin & Chris put an amazing amount of effort in to organising the event and I think the result was there for all to see. I was especially grateful as this gave me the opportunity to try my hand at doing some public speaking to a large audience. It was a great experience and I really enjoyed it. I hope to do more in the near future. So a big THANK YOU to the guys for all their sterling work.
All the people I spoke to seemed to really enjoy the day and everyone seemed so positive about both the conference and SQL Server. It was a really energising experience both giving the talk but also answering people's questions and helping out where I could. This was just as well as frankly I was shattered after a number of really late nights making sure everything went swimmingly.
I'd therefore also like to say a big THANK YOU to all those of you that attended my session, "I/O! I/O! It's off to disk we go...", and especially to those that provided all the feedback. It really helped me understand what I had done well and what hadn't gone so well. I now know about "Zoomit" and will be certainly trying this out next time.
It really is the best feeling and so if you feel like you want to give it a go then please feel free to get in touch and I'll pass your details on.
<James/>
My final slide deck is now attached to this post. However, please note it uses powerpoint 2007 and so you will either need to install the compatibility pack or use the aforementioned product...
|
-
I found this new article written by Sachin Rekhi on Database Unit Testing with Team Edition for Database Professionals.
I think it's an excellent read. The only thing I don't think it covers is what is not included in the product. The most notable piece of Team System Unit Testing functionality that isn't included with DB Pro is Code Coverage i.e. the tab that tells you how complete your test coverage is.
On the upside Gert Drapers has commented numerous times in the forums on this and it looks like it will be in a future release.
Enjoy!
Cheers, James
|
-
I recently put up a post with a unit test for check constraints validating all were defined with the NOT FOR REPLICATION option. I thought it might be helpful to put up a post with tests for all the other object types that can have this option set.
select object_id,name,is_not_for_replication
from sys.foreign_keys where is_not_for_replication <> 1
select object_id,name,is_not_for_replication
from sys.triggers where is_not_for_replication <> 1
select object_id,name,is_not_for_replication
from sys.check_constraints where is_not_for_replication <> 1
select object_id,name,is_not_for_replication
from sys.identity_columns where is_not_for_replication <> 1
I guess you could put a UNION ALL in between all these tests and create one test but I would advise against this as it's harder to verify with any degree of accuracy. Strictly speaking I would say that it also wouldn't be a unit test as it is testing multiple object types.
To verify the results of these tests you could again use the Code assertions in Team Edition for database professionals (DataDude) or use SQL assertions.
If you decide to use sql assertions it might be a good idea to come up with a nice pattern for implementing them.
Here's a basic suggestion:
declare @DbName sysname
,@ObjectName sysname
,@TestType nvarchar(100)
,@TestResult nvarchar(100)
,@Message nvarchar(MAX)
select object_id
,name
,is_not_for_replication
from sys.foreign_keys
where is_not_for_replication <> 1
If @@error <> 0
or @@rowcount <> 0
BEGIN
select @DbName = db_name()
,@ObjectName = N'All Foreign Keys'
,@TestType = N'Schema Test'
,@TestResult = N'FAILED'
,@Message = N'NOT FOR REPLICATION Foreign Keys Test'
raiserror(N'%s : %s : %s : %s : %s',16,1,@DbName,@ObjectName,@TestType,@TestResult,@Message)
END
It would be nice if the raiserror severity was respected and you could set it to say 10 or 15 for info or warning messages without it failing the test. However, this doesn't seem to be the case.
Cheers, James
|
-
We have recently had an internal discussion on service accounts predominantly on the subject of whether it is better to have one service account for sql server that is used in all environments or whether separate accounts should be used in each environment. Perhaps surprisingly everyone came down on the same side (how often does that happen???) and we all seemed to agree on every point. I thought I'd share this with you.
We agreed that the advantages of having separate accounts clearly outweigh the overheads in creating and maintaining them. Below are the reasons why we felt separate accounts are clearly the way to go...
Advantages
- It ensures the integrity of the deployment as dev accounts shouldn't see Test/UAT/Live
- Forces the deployment scripts to be correctly parameterised and helps drive out bugs in the deployment.
- In conjunction with the use of database roles there is no additional overhead for administering database security
- Ensures that events in dev/test don't affect live. For example an account getting locked out in live because a user has failed to login correctly to the account in dev and breeched a group policy.
- More likely to result in a scripted and repeatable build/deployment process which can be re-used in the event of catastrophic failure
Disadvantages
- Overhead in creating all the accounts in the first place
- Additional development work to parameterise all the build scripts
- Can be seen as over-kill by some who view some of these steps as one time only operations.
Anyone care to add / question anything on this list?
Cheers, James
|
-
Recently I had a need to create some unit tests for a change to the database that was under development. I needed to change the database so that the NOT FOR REPLICATION option was set on all keys, constraints, indexes and triggers. Following an agile engineering best practice I created a schema unit test for each of these object types (more on this later) and ran the test. Naturally the test, as I expected, failed. I was now ready to make my change.
I decided to use my sandbox database and generated some sql to create the sql to complete this task. Below is the script I used to generate the change to the check constraints:
/*
Set the results pane to Text
This Script Generates the sql to drop all the constraints from a database and then adds them back with NOT FOR REPLICATION set
*/
SET NOCOUNT ON
SELECT 'SET XACT_ABORT ON'
-- ,''
-- ,''
-- ,''
-- ,''
UNION ALL
SELECT 'BEGIN TRANSACTION'
-- ,''
-- ,''
-- ,''
-- ,''
SELECT 'Alter Table '+QUOTENAME(SCHEMA_NAME(ta.[SCHEMA_ID]))+'.'+QUOTENAME(ta.NAME)+' DROP CONSTRAINT '+QUOTENAME(cc.NAME)
-- ,SCHEMA_NAME(ta.[SCHEMA_ID]) TableSchemaName
-- ,ta.NAME TableName
-- ,cc.NAME CheckConstraintName
-- ,cc.definition CheckConstraintDefinition
FROM sys.check_constraints cc
JOIN sys.tables ta
ON cc.parent_object_id = ta.[OBJECT_ID]
WHERE is_not_for_replication = 'false'
UNION ALL
SELECT 'Alter Table '+QUOTENAME(SCHEMA_NAME(ta.[SCHEMA_ID]))+'.'+QUOTENAME(ta.NAME)+' ADD CONSTRAINT '+QUOTENAME(cc.NAME)+' CHECK NOT FOR REPLICATION '+cc.Definition
-- ,SCHEMA_NAME(ta.[SCHEMA_ID]) TableSchemaName
-- ,ta.NAME TableName
-- ,cc.NAME CheckConstraintName
-- ,cc.definition CheckConstraintDefinition
FROM sys.check_constraints cc
JOIN sys.tables ta
ON cc.parent_object_id = ta.[OBJECT_ID]
WHERE is_not_for_replication = 'false'
UNION ALL
SELECT 'COMMIT TRANSACTION'
-- ,''
-- ,''
-- ,''
-- ,''
The abridged result set looks like this (ran this on adventureworks)
-----------------
SET XACT_ABORT ON
BEGIN TRANSACTION
Alter Table [Production].[ProductListPriceHistory] DROP CONSTRAINT [CK_ProductListPriceHistory_EndDate]
Alter Table [Production].[ProductListPriceHistory] DROP CONSTRAINT [CK_ProductListPriceHistory_ListPrice]
Alter Table [Production].[ProductListPriceHistory] ADD CONSTRAINT [CK_ProductListPriceHistory_EndDate] CHECK NOT FOR REPLICATION ([EndDate]>=[StartDate] OR [EndDate] IS NULL)
Alter Table [Production].[ProductListPriceHistory] ADD CONSTRAINT [CK_ProductListPriceHistory_ListPrice] CHECK NOT FOR REPLICATION ([ListPrice]>(0.00))
COMMIT TRANSACTION
This resulting script did what I was after so I ran it against my sandbox. I was then able to re-run my tests. The tests ran through cleanly ... which was nice. I could then script out the affected objects which is much easier now SP2 has re-introduced the one file per object scripting functionality and check in the change.
The Test
declare @rc int
,@er int
select object_id,name,is_not_for_replication -- The test
from sys.check_constraints where is_not_for_replication <> 1
select @rc = @@Rowcount
,@er = @@Error
If @rc >0 or @er <> 0
Begin
RAISERROR('NOT FOR REPLICATION CHECK CONSTRAINT TEST FAILED',16,1) -- SQL Assertion
End
The above test introduces two concepts
-
The database unit test
-
The Verification of the test
Verification in unit testing is a process of test followed by assertion. In this instance I have used a SQL assertion. I.e. I have validated the results of the test with a sql statement. This could have been done by running the sql test against the database in a c# application and testing the resulting ADO.NET dataset. This would be known as a c# or code based assertion but I don't have the skills (yet) to do this myself.
I have also seen people create rigs/harnesses/extensions to nunit to call sql statements or used more database friendly unit testing frameworks. My colleague Howard van Rooijen has mentioned mbunit as an alternative.
However, one recent development in this area has been Microsofts Team Edition for database professionals product. This plugs into the Team Test framework of Team System and offers a Code Assertion option. It's actually very friendly, gui/designer based, and I believe it is extensible whilst also hooking into the code coverage and unit test generation functionality. However, I think I'll cover this in another post.
Cheers, James
|
-
On Thursday 26th April Conchango hosted meetings of the London SQL Server User Group and the London SQL Server BI User Group. For those that aren't familiar with these groups they are external communities much like our own that are run independently by enthusiasts for the respective technology. Both groups emanate from Tony's original site sqlserverfaq.com.
The following people presented:
Microsoft BI UG
Reed Jacobson - Hitachi - Performance Point Budgeting and Planning
Allan Mitchell MVP - Konesans - Loading Dimensions and Fact Tables with SSIS
David Francis - Conchango - Performance Point Monitoring and Analytics
Microsoft SQL Server UG
Andras Belokosztolszki- Red-Gate - Managing Change in SQL Server
James Rowland-Jones - Conchango - Agile Database Development using Team Edition for Database Professionals
Simon Sabin MVP - TotalJobs Group - Service Broker
Tony Rogerson MVP - Independent Consultant - Parameter Sniffing
The event itself was initially heavily over-subscribed and was very well attended with over sixty people turning up on the night. Speaking with Simon we are hoping to set another one up in June.
It was my maiden voyage presenting externally and whilst I was very nervous I enjoyed the experience immensely. I'd strongly recommend it to anyone to have a go.
I have attached the slides from my presentation to this post. I hope you find them useful and informative.
Cheers, James
|
-
First we had sp2 (build 3042)...
Then came sp2a (still build 3042 but 3043 if you looked at the dlls)
A hotfix was then released to bring those on old sp2 up to sp2a (which took the build number to 3050)...
Now we have the post sp2 cumulative hotfix release taking us to a whopping build no of 3152!
Details (and downloads) of which can be found here
In short it would appear that a regression has taken place between builds 2214 and 2219 and these fixes didn't make it to sp2.
I have cut and pasted the hotfix list for this rollup patch for your convenience below
Bug No Description
| 50000783 |
When you define the value of the AdminTimeout property and the value of the ServerTimeout property, a profiler trace stops unexpectedly. |
| 50000784 |
When you make a data manipulation language (DML) change to a published table, error 2812 occurs. |
| 50000785 |
When you create a cursor by using an SQL statement that uses the OPTION (RECOMPILE) hint, error 16943 occurs. Error 16943 indicates that the table schema changed. This problem occurs when you retrieve information from the cursor location. |
| 50000786 |
A memory leak exists in the MSOLAP provider (Msolap90.dll). |
| 50000787 |
When you configure the Scheduler component to use a time-out value that is less than 15 milliseconds, you experience high CPU usage. |
| 50000809 |
The LazyWriter process uses lots of CPU resources. This problem occurs on a non-uniform memory access (NUMA)-enabled x64-based server that is running SQL Server 2005 x64 Edition. |
| 50000810 |
When you update a view through a cursor, you cannot make a CSEQUAL request or a TSEQUAL request for rows that are extended by using NULL values. |
| 50000873 |
When you run an application in SQL Server 2005, error 8624 occurs. Error 8624 indicates that the cycle check failed. |
| 50000874 |
When the stored procedure runs outside an explicit transaction, a temporary table in a stored procedure is not cleaned up correctly. |
| 50000889 |
When you run a Microsoft Office Excel data mining operation, you receive the following error message:
The 'MINIMUM_DEPENDENCY_PROBABILITY' data mining parameter is not valid for the 'Table1_283833_NB_413946' model |
| 50000939 |
The Maintenance Cleanup task uses an incorrect age group for cleanup operations. |
This is disappointing news indeed and I fear not the end of the story. We'll have to just wait and see what else comes out.
Many thanks to Darren for the heads up!
|
-
Well it isn't likely to cause quite the same stir as Y2K but I thought it was a significant date for everyone's diaries... On the 8th April 2008 Microsoft withdraws mainstream support for SQL Server 2000. Look here if you don't believe me. That's not very far away at all. This gives everyone who is currently using SQL 2000 just over a year (and possibly only one budget forecast / round) to migrate to the new platform unless you wish to pay for extended support from the mighty M$. In my experience people can get quite blasé about support from Microsoft and have been known to adopt a "who cares" attitude. To some extent I can understand that; after all when was the last time you had to phone them up for support? However, this is SQL Server we are talking about - that means data. If and when you have a problem accessing your data I know I wouldn't want to be the DBA turning round to the board or CTO explaining that we have an unsupported platform holding all the company data. I think it's fair to say that if you have read this you have now been forewarned... There is one question still in my mind about upgrades though and that is; what support is there for SQL 2000 DTS packages that have been imported into SQL 2005? Recently I ran the SQL 2005 Upgrade Advisor on a clients database server as I was looking for issues with their DTS packages. There was one message there that raised some concerns especially when thinking about the imminent end of mainstream support. It said: SQL Server 2000 Data Transformation Services Advisory SQL Server 2000 Data Transformation Services is deprecated You must completely migrate existing SQL Server 2000 Data Transformation Services (DTS) packages to SQL Server 2005 Integration Services packages before support is withdrawn. Source Type: MSDB Package From this message it would imply that importing legacy DTS packages into SQL Server 2005 will not be sufficient for you to keep receiving ongoing support. It quite clearly states that the package must be migrated. It also raises important considerations regarding on-going support in Katmai. Will the downloadable DTS designer still be available? What will the suppport be for the Execute DTS 2000 task? There's a lot of questions out there. For the official word from M$ on backward compatability click here. To me though, the answer is this: It's time to get a wriggle on - especially if you have a large number of SQL 2000 instances. It will require investment, both in terms of time and money but also people and process; so my advice would be start planning, budgeting and learning! These kinds of upgrades don't happen over-night. The regression testing alone can be a very intensive process. As we all know, upgrades are a lot of work! My one piece of advice to you here is to ensure that you have identified and agreed the acceptance test criteria for a successful upgrade before you perform the upgrade. This, I think, is the key step in the upgrade process:- define success. There is also some good news. Microsoft have provided the SQL 2005 Upgrade Advisor to help you identify issues with your installations. Additionally, DTS packages don't have to be migrated straight away. You can just import them and execute them as is. There is also a package migration wizard to help you perform your migration although I personally prefer a more holistic approach (see below). Furthermore, SQL Server 2005 Service pack 2 is also just around the corner which will mean your migration should have a very stable target platform to migrate to. To fully leverage SSIS and make the most of the platform will require careful consideration. Things you did in DTS you can and would do differently in SSIS. It's quite a steep learning curve but there are some great resources out there for SSIS notably sqlis and of course this site. My advice would be to take the time to really understand how this platform works and what it can do for you before implementing your own migration of DTS to SSIS. To me there is little point in upgrading technology without looking to use the new features and ways of working. Just make sure you have a plan to do it before your support from Microsoft expires... So the question to ask yourself is this "Is now the time to be (finally) moving to SQL Server 2005?"
|
|
|
|