SQL Server Sizing, HA and Performance hints has a blog out on the –T 1117 trace flag that I happened upon; there has been a lot of info about this trace flag, but I never really paid much attention to it outside of the context of tempdb. This blog reminds that the flag, when set, can affect file growth on all files for a database in a given instance, which I missed previously. So if you have a non-tempdb database as I have below and set the files to grow by 200MB for instance, the trace flag does in fact grow these files using the proportional file algorithm as the author describes. Below I see mine grow using the simple script here: use Test GO SELECT CAST('' AS char(7075)) AS col INTO testTbl FROM sys.syscolumns a, sys.syscolumns b .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }   My user database does grow in fact by 200MB and both files are included, which I never knew. The bold line at the bottom of his post, however, does pretty much hit the nail on the head, and that is to go ahead and size the files fully if possible from the get-go. Sometimes this isn’t possible, so be careful when shrinking and growing files. This is also well documented, and wreaks havoc on fragmentation. Thanks, Lee   --------------------- Note: This post has not been approved by an MCM.     http://blogs.technet.com/technet_blog_images/b/sql_server_sizing_ha_and_performance_hints/archive/2012/02/09/ sql-server-2008-trace-flag-t-1117.aspx http://blogs.technet.com/technet_blog_images/b/sql_server_sizing_ha_and_performance_hints/

Lee posted on June 22, 2011 13:09
Microsoft SQL Server Integration Services (SSIS) is really a great tool in my opinion. Based on my experience to date using it (pre-SQL 2005 release, with the first CTP in the first quarter of 2005 me and an colleague Tom Davis were doing “Tech Nights” at Software Architects, now Sogeti…wow I’ve used it six years now I guess), it’s solid, and a substantial improvement over DTS.  Is it what we want it to be yet? Probably not, but it will get there someday and hopefully be as feature-rich as the other popular ETL tools.  One thing that it allows the practitioner to do is various type of customization, either with the script task, script component, custom component, custom assemblies, or programming the API directly. This is a good thing and a bad thing. Bad because many experienced ETL developers subscribe to the “hands off the keyboard” approach to ETL; this rules out coding of any kind.  There are various reasons for this that I will neither argue nor hash out here, but suffice it to say that traditional data warehouse folks who used the tools for many years relied many on delivered tasks and components and did not look to code and customize as a general rule. Good, on the other hand, because if the particular tool is not available…just build it! Imagine for a moment a process that requires you to create 500 data flows, either individual text file source to database destination, or database table to text file.  How would you split it up to manage it? 50 packages with ten flows?  100 packages with five? Or maybe one package with 500?  You get the picture, and it’s not pretty. Not only that, but after you build it, try managing it right?  Sounds like a nightmare to me.  I’m going to show you a way to create all of this functionality in a single package, and you never need to map a column or drag a connection one.  All of the transformations are handled, all of the data types and string lengths taken care of, all built dynamically and parameter-driven by a set of database tables. Sweet! This series - Dynamic SSIS Packages- is about programming the SSIS Application Programming Interface, or API. I thought about sending this off to SQLServerCentral, one of these online tech magazines, or some of the other outlet, but if you follow me at all, you know that I gave up on those sites and just post my stuff here.   To my knowledge, there isn’t a tutorial on this out on the web, or a series of any kind yet, so that’s one reason that I wanted to put this together…maybe there is, but I thought that I would write this nevertheless with the hopes of helping someone out as well as learning more about it myself. Thanks much to Kenny Chung – a senior database developer from Dallas who created the particular concept and code in DTS and SSIS whom I worked with at Alcon – for letting me “take over” the code so to speak.  Kenny is a great programmer and a good friend and I appreciate his insight and creativeness in building the processes that I will demonstrate in this series.  These packages are truly “dynamic” in nature. Here’s what I will blog about in this series in the coming weeks: 1.     Introduction (this post) 2.     Database design and setup 3.     Database to a flat file 4.     Flat file a database 5.     A in-depth look at the code I may have more or less so we’ll see how it goes. What I want to do right off (after design and setup), however, is get the code out for you to get it installed. After we have it up and running, then we’ll look at it closer to see what’s happening.  I hate to do a step-by-step but leave out being able to actually see something happen, so I’ll give this method a try and you can let me know how it works for you. My obligatory caveat: I do not recommend using custom API packages unless 1) there is a great need for customization, or 2) it substantially makes a series of tasks easier to manage.  It is also nice to have someone in the department familiar with vb.net or c# code as well; you would not be advised to code something like this without someone available to debug or modify. About ten years ago, I worked a SQL Server gig at Jobs.com.  A co-worker there mentioned something one day about “Knowledge is Power”, but a few years ago I came up with my own saying, and that is “Sharing is Power”.  Jobs.com actually went out of business (gee I wonder why) and the domain is now owned by Monster I believe. With that kind of thinking in the IT shop, go figure eh?  Anyway, I look forward to sharing this concept, and while it’s not proprietary or new, I hope to present in such a way that you can gain a better understanding about this great gift of the SSIS API to create your own custom packages. Thanks for reading,Lee Everest   -------------------------------    

Posted in: SQL Server 2008 , SSIS  Tags:
Data Tier Applications in VS 2010 seem to have taken on some nice features as you use it with SQL Server 2008 R2.  One thing that I like is the ability to do quite a bit inside of Visual Studio rather than in SQL Server Management Studio (SSMS).  You can do all of your development inside of VS 2010 – even easier than in VS 2008 since there was no Data-Tier Application project as there is now.  So, what you can do is create all of your objects inside of VS 2010 as a part of an application, and then bundle-up all of the objects and deploy to a database server from that wacked-out .dacpac extension that they’ve created.  Very nice!  Has me thinking…I wonder if someday all dev work, whether it be c, c#, vb.net, tsql, etc. will be based out of one tool?  Seems they’re going that direction, and have finally gotten close.  Let’s take a look:   VS 2008   VS 2010   I read somewhere that you could add data for, say, lookup values, but I forgot to bookmark it, so I wasn’t sure what the correct method for doing this.  I checked Books Online and didn’t find it readily; what I did find, however, was the Pre-Deployment and Post-Deployment folders in the project.  This is super-handy for adding just what I need – a place to automatically add domain, or lookup, or static, values to my database tables where necessary.  Check out these screenshots. Note that I’ve added a script to my Post-Deployment folder – it looks by default in the project folder in the Script.PostDeployment.sql file, in our case, since we want to do a post deploy operation by inserting my values, so we add our script file (StaticValues.sql) in the same location as well: My script for this demo has something super simple.  /* Scipt: StaticValues.sql Reason: Lookup Values using Post-Deployment Database: DAC Date: 12/3/2010 */ USE DAC GO INSERT INTO [DAC].[dbo].[Table1] ([column_1] ,[column_2]) VALUES (1 ,100) GO INSERT INTO [DAC].[dbo].[Table1] ([column_1] ,[column_2]) VALUES (2 ,200) GO INSERT INTO [DAC].[dbo].[Table1] ([column_1] ,[column_2]) VALUES (3 ,300) GO When I build the project, I get the following:   Here’s the deploy – note that VS 2010 runs my .sql file using sqlcmd syntax: ------ Build started: Project: DAC, Configuration: Debug Any CPU ------   DAC -> C:\Documents and Settings\Lee\my documents\visual studio 2010\Projects\DAC\DAC\sql\debug\DAC.dacpac ------ Deploy started: Project: DAC, Configuration: Debug Any CPU ------ Pre-Deploy: Starting script execution…     : (1 row(s) affected)     : (1 row(s) affected)     : (1 row(s) affected) Pre-Deploy: Finished script execution. Validating upgrade Preparing DAC metadata in the SQL Server instance 'S03777\SQL2K8' Preparing deployment script Creating database 'DAC_1_0_0_0__129358633652284695' Creating schema objects in database 'DAC_1_0_0_0__129358633652284695' Registering the DAC in DAC metadata. Setting database 'DAC' to read-only mode Disconnecting users from database 'DAC' Preparing scripts to copy data from database 'DAC'  to database 'DAC_1_0_0_0__129358633652284695' Disabling constraints on database 'DAC_1_0_0_0__129358633652284695' before populating data Inserting data from database 'DAC' to database 'DAC_1_0_0_0__129358633652284695' Enabling constraints on database 'DAC_1_0_0_0__129358633652284695' Setting database 'DAC' to read-write Renaming database 'DAC' to 'DAC_1_0_0_0__129358633654000310' Renaming database 'DAC_1_0_0_0__129358633652284695' to 'DAC' Upgrading DAC metadata to reflect the new DAC version Previous copy of database DAC is available as DAC_1_0_0_0__129358633654000310. Post-Deploy: Starting script execution… Post-Deploy: Finished script execution. ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========     Thanks for reading, Lee   -------------------------------- “Deck the harrs with boughs of horry, fa ra ra ra ra, ra ra ra ra”      http://msdn.microsoft.com/en-us/library/ee210546.aspx

Posted in: .Net , SQL Server 2008  Tags:
Lee posted on November 30, 2010 23:29
If you're thinking about getting Microsoft certifications and need some tips or advice for doing so, go check out Sandeep's blog for some good info.  And I agree - one thing that I have been harping about to my students is that they need to go get a cert as soon as possible.  Rather than go find some practice tests that give away the answers almost verbatim, get on board with these guidelines and make the certification work for you; otherwise, it may look good on paper, but that's about it. But, do it sooner rather than later.  A certification next to your name on your resume looks really sweet I promise. Recently I have begun upgrading my certifications. I really don't want to, but my company pays for them and it's mostly the same stuff as before with a few minor changes, so I'll go get them.  I got my first cert before 2000, so I've taken a few tests.  My advice to go along with his blog post...get wired-up with the software that you need, and actually investigate the test questions and material as best as you can.  I remember studying for the Windows NT 4.0 Workstation exam for example. I loaded up a copy at home, and started getting familiar with the operating system.  Not a bad way to learn, really.  Remember that certifications alone won't get you a job, but between two evenly-matched candidates, the one with the certification will win-out most of the time.  The dedication and willingness to put forth the extra effort doesn't go unnoticed; when I help my company out by interviewing candidates,  I always look to see if he or she has a SQL Server certification. So, good luck with your certs, and let me know if his list helped you out. Lee Everest   --------------------- ‘It's 106 miles to Chicago, we've got a full tank of gas, half a pack of cigarettes, it's dark, and we're wearing sunglasses.’       http://blogs.msdn.com/b/sajoshi/archive/2010/11/29/prepare_2d00_for_2d00_microsoft_2d00_certifications.aspx

I was dorking with Contig for checking fragmentation of SQL Server log files, and it seems to work… and not work at the same time, if that is possible. Here’s the skinny on Contig v1.55, if you haven’t seen this little tool, downloadable from Microsoft: There are a number of NT disk defraggers on the market, including Winternals Defrag Manager. These tools are useful for performing a general defragmentation of disks, but while most files are defragmented on drives processed by these utilities, some files may not be. In addition, it is difficult to ensure that particular files that are frequently used are defragmented - they may remain fragmented for reasons that are specific to the defragmentation algorithms used by the defragging product that has been applied. Finally, even if all files have been defragmented, subsequent changes to critical files could cause them to become fragmented. Only by running an entire defrag operation can one hope that they might be defragmented again. Contig is a single-file defragmenter that attempts to make files contiguous on disk. Its perfect for quickly optimizing files that are continuously becoming fragmented, or that you want to ensure are in as few fragments as possible. Check this – first I am going to pick a database log file, shrink it to remove any fragmentation (VLFs), confirm the fragmentation with Contig and then fragment the hell out of it.  Then, look at it with Contig, defrag with Contig, and then check it again via DBCC Loginfo. DBCC SHRINKFILE (2,0) GO   FileId FileSize StartOffset FSeqNo Status Parity CreateLSN 2 253952 8192 8246 0 64 0 2 253952 262144 8247 0 64 0 2 253952 516096 8248 0 64 0 2 278528 770048 8259 2 64 0 We know the file looks pretty good with only 4 VLFs, so let’s run Contig. It’s gold as well: Let’s run the frag script to frag the hell out of the file (you like that one didn’t you?): SET NOCOUNT ON DECLARE @cntr int = 0 DECLARE @size varchar (20) = '3000' DECLARE @filesize varchar (20) = '3000' DECLARE @incr varchar (2) = 'KB' DECLARE @str varchar (max) WHILE @cntr < 200000 BEGIN SET @str = 'ALTER DATABASE [Test] MODIFY FILE (NAME = N''Test_Log'', SIZE = '+@size + @incr+')' EXEC (@str) SET @cntr+=1000 SET @size = cast(CAST(@filesize as int) + @cntr as varchar (20)) END GO Now run DBCC Loginfo again. It’s fragmented with 730 virtual log files (snippet here only): FileId FileSize StartOffset FSeqNo Status Parity CreateLSN 2 253952 8192 8246 0 64 0 2 253952 262144 8247 0 64 0 2 253952 516096 8248 0 64 0 2 278528 770048 8259 2 64 0 2 458752 1048576 8260 2 64 8.259E+18 2 458752 1507328 0 0 0 8.259E+18 2 458752 1966080 0 0 0 8.259E+18 2 655360 2424832 0 0 0 8.259E+18 2 253952 3080192 0 0 0 8.259E+18 2 253952 3334144 0 0 0 8.259E+18 2 253952 3588096 0 0 0 8.259E+18               ….             Use Contig to see what’s up with the file – something is, because it’s showing a lot of fragmentation: Just for the fun of it, I’m going to run the –v option to try and use the tool to defrag the log file.  From this screenshot, it looks like it did something: But when I run DBCC Loginfo again, the VLFs are still inside of the file.  I’ll need to run the shrink file script from the beginning of this blog to clear out the VLFs.  So what have we accomplished?  Good question. My guess is that this tool appears to actually have moved the file to another block on the disk; it’s not fragmented across the disk, however, we can see that it is still fragmented inside of the log file. In part 3, I’ll continue…have some more geeking with fragmentation inside of transaction log files.  I’ve got a real cool one up next.  Hell yeah! Thanks for reading   Lee Everest   ------------------------- Are we beating a dead horse with all of this transaction log stuff?    http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx

Lee posted on November 16, 2010 14:28
Sankar Reddy had a nice blog awhile back on forward records, and there are some other good ones out on the web about the subject as well.  The first time that I read about this topic was back in 2001 or 2002 in Ken England’s Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook, a really good book back then, and even today still contains some very relevant information (like the chapter where he talks about…forwarded records :)).  While there is much out on the web, as Sankar points out, there is no need to rehash or regurgitate the background info; however, just to make sure everyone is down with what we’re talking about, let’s review: a forwarded record occurs in a heap table (a table with no clustered index) as a result of a change made to the table schema or table data after its original creation.  Because the change can’t be handled by the way the table data is laid down from either the initial or some previous load, a pointer (RID) to a new page for “overflow” data is created, specifically for a varchar column.  This can cause more reads and IO due to this type of traversal whilst performing DML on the table.  Not good in other words.  Slower queries, more CPU required, and more disk I/O can be seen with a table containing forwarded records to one that does not.  The best solution to avoid this scenario would be to, of course, assign a clustered index on the table and be done with it.  If you can’t, you have to remove this type of fragmentation from the table.  There are various scripts out there to do this, so check Google! for one to remove those records. In Sankar’s post, he does an ALTER statement and adds a column to create his forward records. My post today is to remind that while adding a column is certainly a way to create these forward records, I believe that they most often occur by issuing an UPDATE to a variable-length column after the initial data load to the table. Let’s check it out quickly – run the following: USE master GO CREATE DATABASE Test GO USE Test DROP TABLE HeapWForward GO CREATE TABLE HeapWForward (id int ,string varchar (400) ) GO PRINT 'A heap table has been created. Wooo hooo!' GO INSERT INTO HeapWForward SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.id) AS Id, ' ' FROM sys.syscolumns A, sys.syscolumns B GO SELECT forwarded_record_count AS ForwardRecordCount_Before FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'HeapWForward') ,NULL,NULL,'Detailed') as a GO UPDATE HeapWForward SET string = REPLICATE('a', 400) GO SELECT forwarded_record_count AS ForwardRecordCount_After FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'HeapWForward') ,NULL,NULL,'Detailed') as a GO Notice that lots of forward records appear after the update. Sure, an ALTER TABLE with ADD column will produce them, but in online and batch SQL processing, you’re more likely to get them from something such as the above.  For instance, if you use temporary tables and load data and subsequently UPDATE an existing column, you will get forward records. By the way, Microsoft says that read ahead reads will actually decrease with fragmentation, since the read ahead manager can’t read in larger chunks. The same goes for this type of fragmentation as well.  Read-aheads are desirable because this is the mechanism to cache pages and anticipate what the query will need in order to finish in an optimal time frame.  A perfmon counter to use to test this is the Avg Disk Bytes Read: We see this in perfmon very clearly when running a SELECT full scan.  Here’s a scan issued to the table with the data pre-populated - lots of  bytes, and lots of read-aheads: Here’s the same query with the forward records. Notice the great decrease in the Avg. Disk Bytes/Read counter.   Checking STATISTICS IO, in the below image, the first result is from a scan of all records, the second a full scan with forwarded records, and the third with the UPDATE HeapWForward already issued (pre-populated). Notice the read-aheads are much greater with the table already updated; the table with the forward records has many more logical reads, physical reads, with fewer read-aheads due to the fragmentation present.  As important, experiment with sys.dm_db_index_operational_stats; you’ll find that forward records, because SQL Server must traverse more pages, will also cause a greater accumulation of row locks and page locks, and this can be seen as well very clearly. Pretty cool, eh?     Thanks for reading, Lee Everest   ---------------------------- I know. We really didn’t need another forward record post, did we?     Sankar’s blog post - http://sankarreddy.com/2010/03/how-can-i-tell-if-a-sql-server-system-is-affected-by-forwarded-records/ Microsoft SQL Server 2000 Index Defragmentation Best Practices - http://msdn.microsoft.com/en-us/library/cc966523.aspx Microsoft: http://msdn.microsoft.com/en-us/library/ms191475.aspx

One little-know factoid that I learned from the Microsoft RAP folks back a couple of years ago was to exclude the SQL Server files from the virus scan on the server; those tools may actually try to scan or even open some or all of the files located there, and that’s not good. For most installations, I recommend excluding either certain files or just exclude the entire folder from virus protection.  In the screenshot below, I have opened the popular Symantec tool and navigated to the Change Settings >> Centralized Exceptions >> Configure Settings where I can add whatever I wish so that it will not get scanned.  You may have some other virus protection, so check the help section on how to do exclusions. All of them have this feature I am certain. Practice first on a test box, and then put it into production.  In a high-transaction system, anything that you can identify to help in the least is important, and this is one of them; cycles are a premium, and on these boxes you take all that you can get for sure.  It’s also recommended by Microsoft so that’s good enough for me (most of the time haha).       Thank you, Lee Everest   -------------------------- I know what you’re thinking – Oh boy, another “best practice”…

Posted in: SQL Administration , SQL Server 2008  Tags:
You might get the following error when installing reporting services using SQL Server 2008 R2.  From the event viewer>system tab: Unable to bind to the underlying transport for 0.0.0.0:80. The IP Listen-Only list may contain a reference to an interface which may not exist on this machine.  The data field contains the error number.   I’m not sure why, locally, I can’t use TCP port 80, but simply changing this in the Reporting Services Configuration Manager “Web Service URL” to something else and you’ll be gold.  Thanks much Lee Everest   ---------------------------- Rumack: Can you fly this plane, and land it? Ted Striker: Surely you can't be serious. Rumack: I am serious... and don't call me Shirley.

Posted in: SQL Server 2008  Tags:
lee posted on October 25, 2010 12:24
We’ve decided, for my course at North Lake College this fall, to use Microsoft SQL Server 2008 Reporting Services Step by Step book: This was written by Stacia Misner, and it’s a good one. I looked at four or five books before deciding on one, and found this one to be the best of the best. Stacia has done some good things lately for the SQL Server community, including the free e-book that introduced SQL Server 2008 R2 in .pdf form, available for download here. This book will not have Report Builder 3.0 I do not believe, since it came out afterwards, which is somewhat significant since they changed the entire thing, but that’s OK – we’re not going to be using this feature much and I’m not so sure that I recommend it anyway.  If you need a book on RS and you haven’t purchased one as of late, I’d get this one before the others.  Thought it covered a lot and was easy to read.  And, since I haven’t done a RS gig in about three years, it was perfect for me since I’m not up on the new stuff. Thanks for reading! Lee Everest   -------------------------       http://blogs.msdn.com/b/microsoft_press/archive/2010/04/14/free-ebook-introducing-microsoft-sql-server-2008-r2.aspx

Posted in: SQL Server 2008 , Etc. (Off-topic)  Tags:
Introduction I’m at a client getting ready to give my .02 cents on why their SQL Servers are not running to well, and I happened to notice multiple transaction log files for their databases.  We know that multiple data files for some databases, including tempdb, has advantages in performance and administration. This is well documented. However, I was wondering why their setup included multiple log files, so I started thinking about this; I had not remembered reading about extra throughput or anything when adding multiple transaction log files to a database, so I wasn’t sure what their goal was in having this setup. Actually seemed like a waste of resources to me. So, I decided to find out more about the transaction log (can I call it the “tlog” from here on out? Thanks) as it relates to single vs. multiple files, and exactly what happens, or doesn’t happen, when these additional files present. Ah, yeah. It's just we're putting new coversheets on all the TPS reports before they go out now I thought that I would find some good info on MSDN or around the internet, but was surprised a bit to find little information on this particular subject.  So I called up a friend at Microsoft and his answer was: SQL only writes to one ldf file for a database at a time.  Multiple log files are only useful for working around running out of space on your log volume. [1] What he’s saying here is that extra log files are normally used as a fail safe for the first tlog – SQL Server does not write to all tlog files at the same time, nor does it do round-robin type writes as it does with multiple data files.  Then I did happen to come across some work from Paul Randal on sizing the transaction log, and he also mentions the same thing for the most part: Add more transaction log files. As a general strategy for managing the size of the log, this is not good. Extra log files have zero effect on performance (the myth that SQL Server writes in parallel to the log files is just that - a myth) and make management more tricky. However, if your log fills up for some out-of-the-ordinary reason, then adding another log file may be the only way to allow the database to keep running. If you have to do this though, make sure that you remove the extra log file(s) when you can to keep log management uncomplicated.[2] I’m not certain what management complication he’s referring to, other than you would have multiple log files to back up maybe? Not sure. Anyway, the first part of the paragraph pretty much matches what my buddy offered up. And I agree here – over-engineering anything is worthless, so why do it? And that’s exactly what I’m trying to root-out here. By the way, Paul Randal has got some really great stuff on his blog, very impressive.  I read his bio and see that he was with Microsoft for 1000 years, so I figure that he knows what he is talking about.  I’ve also read some of his articles as a subscriber to SQL Server Magazine for the past decade, so I know he’s good with SQL Server internals as well. Check out his blog postings on the transaction log [2] for some really cool threads. Want to go to Chotchkie's? Get some coffee? Being the curious one that I am, all of this info is good and wonderful, but I wanted to investigate just a bit deeper to see if SQL Server really just ignores these files, whether SQL Server writes to them or not, or whatever else I might be able to dig up. I created a database called Test, and it looks kind of like so: Notice that I’ve shrunk the logs to 1MB, but you can create them this size to start off.  I also have them on different vhds, so I know that I/O won’t be running together in case we find something here in our little investigation. Run the following to verify that the log files are all 1MB: SELECT (size * 1.0 * 8.0)/1024.0 AS size_in_mb,* FROM Test.sys.database_files WHERE data_space_id = 0 I now execute the following script and insert some data into a table called LogTable. It has two columns, one of decimal data type, and the other char data type: SET NOCOUNT ON DECLARE @cnt decimal(10,4)=0 DECLARE @rows int=0 BEGIN TRAN WHILE 1=1 BEGIN INSERT INTO LogTable VALUES (ROUND((RAND()* 1000000),0), SPACE(1024)) SELECT @rows+=1 SELECT @cnt = (size * 1.0 * 8.0)/1024.0 FROM Test.sys.database_files WHERE data_space_id = 0 AND [FILE_ID]=5 IF @cnt>1.0 BREAK END SELECT @rows; GO I randomly choose [FILE_ID]=5, but I already know that I could have used 3 or 4 as well.  When the script complete s, I inserted a total of 23461 rows, but yours will most likely vary. Notice now the size of my files when I run the first script: Interestingly, all of the log files have been written to – obviously the first tlog file has most of the writes, but the others are the same size by the time the script hit the BREAK.  Apparently SQL Server did write to more than one log file, but it’s strange that it wrote only 1MB to 32MB for the first transaction log that was created. Subsequent runs indicate a similar behavior, but I ran a few times and found that it hit the BREAK with considerably fewer rows than 23K from the first run. And then there's Tom Smykowski... He's useless. What is SQL Server doing here, and what’s in these additional tlog files? I can only answer the second question, and may have to ping the storage engine team to explain it I suppose. In order to find out what’s going, I went to ApexSQL and downloaded the ApexSQL Log reader tool;  go download the 14-day trial and check it out if you are following along with me. When I consulted at Rent-A-Center a few years ago by the way, they had the Apex suite of tools and I really liked them. I installed and fired up the tool and here’s what I get when I have it inspect the Test_Log2, 3, and 4.ldf files: I get nothin!  So obviously SQL Server is writing some internals stuff here and not info from our INSERT statement(s).  Cool!  Running the tool with only that first log file, I get this, so we know that the tool is working correctly: And there’s IO as well for those log files for disk drives e, f, and g…as we would expect:   Conclusion We’ve seen that 1) SQL Server does not round-robin multiple transaction log files, 2) additional files do get written to, but not with transactional information per-se, 3) whatever is getting written to these tlog files happens at the same time, and is only a fraction of what is written to the first tlog file created for the database, and 4) there is I/O present on the disk when the additional tlog files are written to, but not much. I’m going to keep looking for some answers here, so let’s call this part 1 and I’ll investigate in some more and see what I can find…definitely interesting to me, and I hope you enjoyed the read. Right now I’m fairly convinced at this point that additional transaction log files are of no use in a normal Microsoft SQL Server database setup. Thanks much, Lee Everest ------------------------- I can't believe what a bunch of nerds we are. We're looking up "money laundering" in a dictionary References [1] Unnamed resource from Microsoft haha [2] http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-proper-transaction-log-size-management.aspx [3] http://www.sqlskills.com/BLOGS/PAUL/category/Transaction-Log.aspx [4] http://www.apexsql.com/default.aspx

Lee posted on October 9, 2010 22:28
I've enjoyed geeking a little today with SQL Azure and Sync Framework, but at times I feel that the whole thing is in beta or something.  Having looked at the offering thus far, and playing with the cloud database that I have, I don't really know how someone can put a mission-critical database in it.  But I've been wrong before, so maybe you can.  I haven't had a chance to work with Azure in awhile, but am carving out some time to see what's up! A few things I want to look at further after exploring today, which I may blog about: The Triggers that it lays down - the sync framework process simulates replication, so for each table synchronized, it generates three triggers. tablename_delete_trigger, _insert_trigger, _update_trigger.  Rows are modified and then picked up by the sync and sent either one direction or bi-directionally to your server. Tablename_tracking table - do you actually always get a row each time a sync occurs?  That could be a problem.  If you do a lot of updates, your tracking table will grow at the same rate as the 'base' table. dbo.schema_info - nothing big here, not sure how it works! dbo.scope_config - this table includes a column called config_data. Unfortunately if you do an ALTER TABLE to either table (local or remote) the XML doesn't get updated, so when you try a sync it doesn't work for that column.  This is a problem, and I tried to find a way to resync the sync but to no avail.  Interesting. dbo.scope_info - don't know how it works, but has something to do with the sync_scope_name (namespace maybe?) that you define for your tables group when you declare scope. The Sync Framework Team Blog folks are doing a super job - as are the guys and gals at the SQL Azure team blog - in getting info to us to actually use this new and exciting tool.  My old mentor Don Bishop used to say that a product will either succeed or fail based on the quality of code examples that Microsoft makes available to developers and administrators, and I fully concur.  I suggest that you get over to these sites and see what they're doing; they have plenty of code examples available for examination. I don't know if and when I will be called upon to help with an Azure implementation, but I figure that I'll need to learn the technology in case that I do.  I do see some possibilities here, including scheduling the sync with a SQL Agent job in short intervals to keep two databases synchronized.  Working with this also allows you to get experience with the Entity Framework as well as Linq, so I recommend that you take some time and at least see what all of the buzz is about.  More to come on SQL Azure! Thanks for reading, Lee Everest   ------------------------ "What's the dealio, little lady?"  "My Kiki she's stuck in the tree!"  "Hold my stones"

Posted in: SQL Server 2008  Tags:
I wanted to revisit the MSDN library article on simulating an error output for the script component because I think it's cool, and I had to use it the other day.  Mine is just about the same as their example, but wanted to make sure that you were aware of the gotchas when you set it up.  Rather than some check for values as they did, I simply use a TRY CATCH to trap the error, since who knows what an error actually might be, and trying to code for one wouldn't be a good idea. I'm getting some string from JD Edwards that is delimited by a "double-pipe" as such: INSERT ParseRow VALUES (1, '12345||000111999888||200.00||20101101120000') INSERT ParseRow VALUES (3, '12345||000111999888||200.00||2010110112000023') What's up with the "double-pipe" I have no idea.  I'm still shocked that people do the things that they do for no apparent reason.  Anything however from the As/400 folks doesn't surprise.  Anyway, notice that the last "column" is a date time, the latter has a problem of course since it doesn't match my code for date time: Dim strSplit As String() = strStart.Split(New String() {"||"}, StringSplitOptions.None) Row.AccountID = strSplit(0).ToString Row.AccountNbr = strSplit(1).ToString Row.Balance = strSplit(2) Row.UploadDt = Date.ParseExact(strSplit(3).ToString, "yyyyMMddhhmmss", _ System.Globalization.DateTimeFormatInfo.InvariantInfo) I use the handy Date.ParseExact here and split my string via String.Split(). No problems here, except when it hits that second row it blows with an error: Again, rather than code for errors I just use a TRY CATCH; Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim strStart As String = Row.rowValue Dim strSplit As String() = strStart.Split(New String() {"||"}, StringSplitOptions.None) Try Row.AccountID = strSplit(0).ToString Row.AccountNbr = strSplit(1).ToString Row.Balance = strSplit(2) Row.UploadDt = Date.ParseExact(strSplit(3).ToString, "yyyyMMddhhmmss", _ System.Globalization.DateTimeFormatInfo.InvariantInfo) Catch Row.DirectRowToErr() End Try End Sub The above code is the super-easy part - the PITA is actually setting up the properties correctly to get this thing to work correctly.  So, make sure that you change the Exclusion Group property for BOTH outputs to be the same non-zero number, and most importantly, change the dropdown of the SynchronousInputID to be the same for both outputs.  Actually, you have to do this first or you will not have intellisense, nor will the IDE allow you to do a Row.DirectRowToErr() method; it doesn't know what to direct since it thinks that it needs to just send the data to both outputs.  If you don't change the dropdown in the properties, but set the Exclusion Group to the same value, you'll get into a nasty loop and eventually have to just delete the task and start over. Thanks for reading, Lee Everest   ------------------------------ Karl: I like them french fried potaters. Bill Cox: Yeah

Posted in: SQL Server 2008 , SSIS  Tags:
Administrator posted on October 4, 2010 08:01
Microsoft SQL Server 2008 Service Pack 2 (SP2) is now available for download.  The release notes are quite extensive, so check them out; there are so many that they have them segmented by topic.  I was perusing the list of Integration Services in particular (since that's what I've been working on the past year or so) and it includes eighteen or so fixes, but I couldn't find one that I consistently run into, so we may be good here. Interesting that it looks like about 1/2 of them are related to Script Task. Be careful that you are on the current version and not the revision. How we can have two releases of the release notes out within a week of the service pack I have no idea, but it apparently has been done. A couple of other things... SQL Server Utility. After you apply SP2, an instance of the SQL Server 2008 Database Engine can be enrolled with a utility control point as a managed instance of SQL Server. For more information, see Overview of SQL Server Utility in SQL Server 2008 R2 Books Online. This is a good one, so you can now use the SQL Server Utility on both SQL Server 2008 and SQL 2008 R2 - if you haven't geeked with this feature check it out. It's pretty cool. 15K partitioning Improvement. Introduced support for a maximum of 15,000 partitions in tables and indexes in Microsoft SQL Server 2008 Service Pack 2 in the Enterprise, Developer and Evaluation Editions. For details on how this support can be enabled and disabled, along with recommended operation paramaters, see the white paper Support for 15000 Partitions. 15K partitions?  Seriously someone needs this?  Wow, I have to get up-to-speed on partitioning if we're requesting this functionality these days...   Thanks for reading, Lee Everest   ------------------------------- Halford's new release...dig it!     http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8fbfc1de-d25e-4790-88b5-7dda1f1d4e17&displaylang=en

Posted in: SQL Server 2008  Tags:
Lee posted on September 24, 2010 06:50
In the Microsoft SQL Server 2008 Feature Pack, whichever release that you may choose to download, one of the first mentioned is Microsoft ADOMD.NET.  The description for this part of the feature pack is as follows: Microsoft ADOMD.NET ADOMD.NET is a Microsoft .NET Framework object model that enables software developers to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2008 Analysis Services. ADOMD.NET is a Microsoft ADO.NET provider with enhancements for online analytical processing (OLAP) and data mining. So, handy tip for the day...if you have an ASP.Net application that needs to connect to SSAS, make sure the web server has Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider,  Microsoft Core XML Services (MSXML), and Microsoft ADOMD.Net installed.  I'm seeing more and more folks use this these days - super fast aggregations up on a web page makes things really nice, especially when the alternative is traversing 100 million rows in a table in real time. Thanks Lee Everest   --------------------------- I never had an answer. I guess that's why I'm working at Initech     http://www.microsoft.com/downloads/en/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=b33d2c78-1059-4ce2-b80d-2343c099bcb4#Instructions

Posted in: SQL Administration , SQL Server 2008  Tags:
Someone asked me the other day why SQL Server Management Studio (SSMS) scripts datetime data as binary. I told them I have no idea. Then they asked me whether or not the data would ‘port’ (geezsh we use that port word too much these days, don’t we?) to another server. Now, I didn’t laugh at while standing in front of them, but I did laugh later. Let’s check it out: CREATE TABLE FORMATS( ID int PRIMARY KEY ,Dt datetime ) GO INSERT FORMATS (ID, Dt) VALUES (1, '2010-09-21 00:00:00.000') GO   Now crack open SSMS and go script data from the table (Tasks>Generate Scripts… from the database): INSERT [FORMATS] ([ID], [Dt]) VALUES (1, CAST(0x00009DF800000000 AS DateTime)) For one thing, he forgot to put INTO with the INSERT statement, and may get 1000 lashings from either Joe Celko and/or the SQL Police. So what’s up here? Well, they’ve just cast the data as varbinary/binary(8), no biggie. Why they did this I have no idea, maybe easier to generate script?  Maybe SMO is calling .Net libraries to script the data?  I’ve got a couple of emails off, one to someone that works on the storage engine team for one, and another at the local Microsoft office.  But who knows. I’ll find out and update this post, but truth be told it doesn’t matter a whole lot I guess. Let’s go backwards to the original data type, just for grins and giggles: SELECT CAST('2010-09-21 00:00:00.000' AS binary(8)) Uh oh – something ain’t right: 0x323031302D30392D Now I don’t know much about “birthin’ babies”, but I do know that 0x323031302D30392D does not look in any way, shape, or form like 0x00009DF800000000. So what gives? This is getting even more confusing. No worries. Just cast it to a datetime first, and then cast to a binary(8): SELECT CAST(CAST('2010-09-21 00:00:00.000' AS datetime) as binary(8)) 0x00009DF800000000   Thanks for reading, Lee Everest   ----------------------------------       Flickr Tags: sql server management studio script data,script datetime data type in management studio,binary datetime in management studio script

Posted in: SQL Administration , SQL Server 2008 , TSQL  Tags:
If you use the OLE DB Source, OLE connection manager, and Data access mode of “SQL command from variable”, you might get the following error: Command text was not set for the command object. What I’m guessing that you are trying to do is place TSQL in a table, use an Execute SQL Task to fetch your SELECT statement, place the string in a variable via the “Result Set” tab of the task, and finally, send the variable to an OLE source to run.  This isn’t a bad way to code SSIS packages at all, really; at work we have a package that queries six sources, and we use this type of setup successfully. In ours each of the six queries has the same column names and number of columns, the differences in the queries are only in the WHERE clause. This therefore allow us to use/reuse one package for retrieving data from all sources.  Normally a process calls the package to run all six in a linear fashion, but the beauty of ours is that it allows any one of them to be called at any time of the day as well; we simply send a parameter to fetch the TSQL for the one that we wish to run, and we’re gold.  Nice! So what’s up with this error?  What’s happening is that there is no “metadata”, so to speak, for the package. While the error says the command text is missing, it really isn’t – what is missing is the metadata for the transformation. It doesn’t know the columns to map, so it errors out.  What you need to do is to “seed” a query inside of the value of the variable that you are trying to execute. This query is only a placeholder and won’t be executed unless the source from your Exec SQL task fails to retrieve code, or you run it locally. Notice in the figure that I appended a —Seed query: not used comment. This will not get executed and only serves to gen my metadata.  Notice now when I go to my OLE source, the string shows up, where before I got the error above. (We use a Script Task to parse out the comment, which gives us a “debug mode” to make sure we’re executing the correct SQL String. While debugging we may put the string to a Message Box). Again, this “seed query” wont execute. What will is the query string that I get from my table.  If I go back to the Control Flow, I get this: One interesting item – if I remove my seed string, here’s what I get: But if I turn on the DelayValidation property (set to True) it works.  If however, I change to a table as a source and not the variable, the “metadata” goes away, and I have to plug my seed string back in the variable value property once again when I switch back to the SQL command from variable. Bottom line:  just leave the string in there, it shouldn’t bother you.  If you like you can do a WHERE 1=0 to only get the column names. Thanks much, Lee Everest   ------------------------------ I gotta wake my ass up at six AM every day of this week and drag myself up to Las Colinas. Yeah, I'm doing the drywall up there at the new McDonalds.     Flickr Tags: SSIS error,command text was not set for the command object,sql command from variable,execute a string in SSIS OLE task

Posted in: SQL Server 2008 , SSIS  Tags:
Lee posted on September 16, 2010 14:15
Not really a SQL Server product, but worth mentioning nonetheless, I move around from company to company as a consultant, usually 6 months to a year seems to be the average stay although I was at one place for almost two years. Normally when you get some place you have to set up your workstation with software, permissions, and handy tools. It seems that I always seem to be looking for a good compare tool, but never can find one. Right now I am trying WinMerge from http://winmerge.org/ and I really like it! It’s perfect for what I’m doing, which is simple file compares for code…really handy when you are promoting stuff from server to server and you want a quick down-and-dirty compare.  And of course it is free. There are other options, but this one seems to be working pretty well for me; suggest that you give it a try! Lee Everest   ---------------------------------

Posted in: Etc. (Off-topic) , SQL Server 2008  Tags:

by Lee Everest, M.S.

Info

Poll

Do you use Azure or cloud in your organization?
Show Results

Ads

Search


Month List

Calendar

«  May 2012  »
MoTuWeThFrSaSu
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910
View posts in large calendar

Tags

Disclaimer
The opinions, code, examples, et.al. expressed herein are my own personal opinions and do not represent my employer's view in any way, shape form, or fashion.  All code for demonstration purposes - no guarantees, either written or implied, are made.

© Copyright 2012 Lee Everest's SQL Server, etc. weblog