I've experienced a particular frustration with SQL Server many times over the years: the seeming impossibility of programmatically fetching a varbinary value from a table, and converting it to the string value required by a procedure call in the same batch. One example would be when troubleshooting replication by extracting a xact_seqno varbinary(16) value from the MSdistribution_history table, and using it as the value for the nchar(22) @xact_seqno_start and/or @xact_seqno_end input parameters to the sp_browsereplcmds procedure.
For a long time, I just did this by hand because there didn't seem to be a way to get the value out. Later, I sometimes used the output parameter functionality of sp_executesql to bluff my way through this. However, I just discovered the there is a built-in (but undocumented) function that allows you to transform a varbinary into a string! It's master.dbo.fn_varbintohexstr(). Here's a script that accomplishes the replication troubleshooting task I cited above (I assume here you already know the publication database id and agent id - finding those is another subject altogether):
declare @last_xact_seqno varbinary(16)
,@last_xact_seqno_str nchar(22)
select @last_xact_seqno = max(xact_seqno)
from distribution..MSdistribution_history
where agent_id = 6
and error_id = 0
set @last_xact_seqno_str = master.dbo.fn_varbintohexstr(@last_xact_seqno)
exec sp_browsereplcmds
@xact_seqno_start = @last_xact_seqno_str
,@publisher_database_id = 7
Showing posts with label SQLServer 2005. Show all posts
Showing posts with label SQLServer 2005. Show all posts
Thursday, November 19, 2009
solving frequent replication distribution latency errors
We have many millions of commands a day passing through a 2-node peer-to-peer replication topology. At some point, replication lost its ability to keep up with distribution. After much research, we replicated the issue to Microsoft and were told that our distribution database was too big. They recommended that we set immediate_sync on the publication to false.
To me, it made no sense why this would help. We had replication distribution history latency set to 48 hours - wouldn't transactions be retained for that amount of time regardless of whether they'd been replicated or not?
Luckily, I found this MSDN blog post, which does a great job of explaining the interaction between min transaction history retention, max transaction history retention, and immediate_sync. Our plan now is to set min retention to 8 hours (we need a buffer that will allow us to initialize from backup), max retention to 36 hours, and immediate_sync to false. This will keep all transactions for 8 hours, but replicated transactions after that buffer will be deleted as soon as they're replicated to existing subscribers. Transactions will only be retained for 36 hours if replication gets seriously backed up again.
To me, it made no sense why this would help. We had replication distribution history latency set to 48 hours - wouldn't transactions be retained for that amount of time regardless of whether they'd been replicated or not?
Luckily, I found this MSDN blog post, which does a great job of explaining the interaction between min transaction history retention, max transaction history retention, and immediate_sync. Our plan now is to set min retention to 8 hours (we need a buffer that will allow us to initialize from backup), max retention to 36 hours, and immediate_sync to false. This will keep all transactions for 8 hours, but replicated transactions after that buffer will be deleted as soon as they're replicated to existing subscribers. Transactions will only be retained for 36 hours if replication gets seriously backed up again.
Monday, November 02, 2009
Shrinking data files, part 1
You've probably heard admonishments to avoid shrinking data files, especially on a heavily used database. The standard usage of DBCC SHRINKDATABASE or DBCC SHRINKFILE (not using the TRUNCATEONLY option of these commands) will move any data at the tail of the file to the beginning. The most frequently cited negative consequence of this action is serious fragmentation of the data you moved as data is moved page by page to the beginning of the file with presumably no attention paid to the objects represented by the data. In my case, shrinking data files also caused very heavy I/O load on my staging server, with sustained average disk queues reaching up into the hundreds. Definitely not something I wanted to attempt on a heavily loaded production server.
However, I had a very large database file (>400 GB) from which I had moved a substantial portion (>75%) of the data to a different filegroup. After several attempts to shrink the data file and witnessing the resulting abysmal performance, I found myself wanting to know exactly what was that data at the end of the file so I could move it myself by just rebuilding the indexes. I could then just use DBCC SHRINKFILE with TRUNCATEONLY to recover the space with none of the fragmentation. Most of the indexes in my database can even be rebuilt online using ALTER INDEX . . . REBUILD WITH (ONLINE=ON).
Here's how I found out what was sitting in the back rows of my data file:
First, I already knew that the GAM pages could tell me whether a particular extent is allocated or not. If you don't know what a GAM page is, check Books Online or any one of hundreds of websites and books that can offer more detail. Basically, the GAM is a page in a data file that is a map of the next 500,000 (or so) extents that indicates whether each is allocated or not. There's a GAM page every so often in a data file to indicate the status of the next set of extents. What I wanted to look at was the last GAM in the data file.
As I already mentioned, the data file I'm working with is fairly large, so I wasn't exactly sure how to find the last GAM. I found documentation indicating that the first GAM of each data file is on the 3rd page of the data file (actually page id 2 because of zero-based counting), and another one every 511,230 pages, so I could've probably figured it out by looking at the total size, figuring how many GAMs there were, yadda yadda yadda. What I did instead was look at the last page in the file, and it told me what its parent GAM was!
I found the last page in the data file by getting the total number of pages in the data file:
select size, * from sys.database_files where file_id = 1
Size in the sys.database_files table actually represents the number of pages in the data file. In this case, the number of pages was 51380223. I then ran a DBCC PAGE for the last page in the data file: page id = 51380222 (remember the zero-based counting). Before running that command, though, I first needed to turn on trace flag 3604 so DBCC results would be returned to the client:
DBCC TRACEON (3604)
DBCC PAGE (10,1,51380222,3)
In the DBCC PAGE command above, the first parameter is the database id, second is the file id, the third value is the page you want to view, and the last parameter represents the type of output you want to see.
In the output of the DBCC PAGE command, on the Messages tab of the output (I ran this in grid mode in SQL Server Management Studio), I scrolled down to the Allocation Status section. There was a line that read "GAM (1:x) = ALLOCATED". That x is the page number of the last GAM in the data file! I then ran DBCC PAGE again with that value:
DBCC PAGE (10,1,x,3)
The output of this command included lists of page ranges, marked ALLOCATED or NOT ALLOCATED. I scrolled all the way to the bottom tolook for the last ALLOCATED page range. My output looked something like this:
(1:51372888) - (1:51372960) = NOT ALLOCATED
(1:51372968) - = ALLOCATED
(1:51372976) - (1:51373704) = NOT ALLOCATED
(1:51373712) - = ALLOCATED
(1:51373720) - (1:51374552) = NOT ALLOCATED
Pages 51373720 - 51374552 were already not allocated. I knew that if I ran a DBCC SHRINKFILE with the TRUNCATEONLY option, this range of pages would be removed from the data file, and that would be all that the command would do. However, I need to clear more than 300 GB of space in all , and a few hundred pages wasn't going to cut it. So I decided to see what was on page 51373712, and rebuild that index. Based on my reading, I assumed that when rebuilding, SQL Server would go back to the GAM and other related file allocation pages, and find the first pages in the data file that could hold the rebuilt index. This would effectively move pages from the end of the data file, although I was aware that it could end up removing only one extent worth of pages at the very end. To that end, I again ran DBCC PAGE to find out what was on that data page:
DBCC PAGE(10,1,51373712,3)
The PAGE HEADER section of the output included the following information:
Metadata: IndexId = 1
Metadata: ObjectId = 437676587
Aha! I rebuilt that index and as I expected, it cleared up some bunch of extents at the end of the file. After repeating this process several times, I was able to shrink my file by a sizable margin using DBCC SHRINKFILE with the TRUNCATEONLY option. The db file was actually less - rather than more - fragmented when I was done, and I never encountered those horrible disk queue lengths.
Of course, this doesn't work so well if the last allocated page is from a large index that doesn't allow REBUILD WITH (ONLINE=ON). When I figure out how to get past that, I'll post again.
However, I had a very large database file (>400 GB) from which I had moved a substantial portion (>75%) of the data to a different filegroup. After several attempts to shrink the data file and witnessing the resulting abysmal performance, I found myself wanting to know exactly what was that data at the end of the file so I could move it myself by just rebuilding the indexes. I could then just use DBCC SHRINKFILE with TRUNCATEONLY to recover the space with none of the fragmentation. Most of the indexes in my database can even be rebuilt online using ALTER INDEX . . . REBUILD WITH (ONLINE=ON).
Here's how I found out what was sitting in the back rows of my data file:
First, I already knew that the GAM pages could tell me whether a particular extent is allocated or not. If you don't know what a GAM page is, check Books Online or any one of hundreds of websites and books that can offer more detail. Basically, the GAM is a page in a data file that is a map of the next 500,000 (or so) extents that indicates whether each is allocated or not. There's a GAM page every so often in a data file to indicate the status of the next set of extents. What I wanted to look at was the last GAM in the data file.
As I already mentioned, the data file I'm working with is fairly large, so I wasn't exactly sure how to find the last GAM. I found documentation indicating that the first GAM of each data file is on the 3rd page of the data file (actually page id 2 because of zero-based counting), and another one every 511,230 pages, so I could've probably figured it out by looking at the total size, figuring how many GAMs there were, yadda yadda yadda. What I did instead was look at the last page in the file, and it told me what its parent GAM was!
I found the last page in the data file by getting the total number of pages in the data file:
select size, * from sys.database_files where file_id = 1
Size in the sys.database_files table actually represents the number of pages in the data file. In this case, the number of pages was 51380223. I then ran a DBCC PAGE for the last page in the data file: page id = 51380222 (remember the zero-based counting). Before running that command, though, I first needed to turn on trace flag 3604 so DBCC results would be returned to the client:
DBCC TRACEON (3604)
DBCC PAGE (10,1,51380222,3)
In the DBCC PAGE command above, the first parameter is the database id, second is the file id, the third value is the page you want to view, and the last parameter represents the type of output you want to see.
In the output of the DBCC PAGE command, on the Messages tab of the output (I ran this in grid mode in SQL Server Management Studio), I scrolled down to the Allocation Status section. There was a line that read "GAM (1:x) = ALLOCATED". That x is the page number of the last GAM in the data file! I then ran DBCC PAGE again with that value:
DBCC PAGE (10,1,x,3)
The output of this command included lists of page ranges, marked ALLOCATED or NOT ALLOCATED. I scrolled all the way to the bottom tolook for the last ALLOCATED page range. My output looked something like this:
(1:51372888) - (1:51372960) = NOT ALLOCATED
(1:51372968) - = ALLOCATED
(1:51372976) - (1:51373704) = NOT ALLOCATED
(1:51373712) - = ALLOCATED
(1:51373720) - (1:51374552) = NOT ALLOCATED
Pages 51373720 - 51374552 were already not allocated. I knew that if I ran a DBCC SHRINKFILE with the TRUNCATEONLY option, this range of pages would be removed from the data file, and that would be all that the command would do. However, I need to clear more than 300 GB of space in all , and a few hundred pages wasn't going to cut it. So I decided to see what was on page 51373712, and rebuild that index. Based on my reading, I assumed that when rebuilding, SQL Server would go back to the GAM and other related file allocation pages, and find the first pages in the data file that could hold the rebuilt index. This would effectively move pages from the end of the data file, although I was aware that it could end up removing only one extent worth of pages at the very end. To that end, I again ran DBCC PAGE to find out what was on that data page:
DBCC PAGE(10,1,51373712,3)
The PAGE HEADER section of the output included the following information:
Metadata: IndexId = 1
Metadata: ObjectId = 437676587
Aha! I rebuilt that index and as I expected, it cleared up some bunch of extents at the end of the file. After repeating this process several times, I was able to shrink my file by a sizable margin using DBCC SHRINKFILE with the TRUNCATEONLY option. The db file was actually less - rather than more - fragmented when I was done, and I never encountered those horrible disk queue lengths.
Of course, this doesn't work so well if the last allocated page is from a large index that doesn't allow REBUILD WITH (ONLINE=ON). When I figure out how to get past that, I'll post again.
Friday, January 23, 2009
Partition Management....(Don't forget the Management)
You'd think by now I'd know better than to cut corners. But for some reason, I still somehow seem to decide once in a while for short term gains over long term benefits...
A while back I was overloaded with stuff so I set up a year's worth of new monthly partitions in one of our reporting databases. I did not, however, chose to create any nice job to automatically create new partitions or anything like that, as I did not have time (or so I thought).
Well, 'A while back' is now 18 months ago.... Hmm... 6 months of data in one partition. Ouch.
Might not be a big deal with Oracle, but it's SQLServer 2005. Sadly, just splitting 1 day of data into a new partition takes 10 minutes and 9 GB of transaction log space. Very depressing. (At least I tested and timed it before I tried to split one month's data into a new partition).
Kills me some times. Don't put off till tomorrow what you can do today, if tomorrow might become next year... ;)
Hopefully I'll come up with a slick way to split those partitions faster... we'll see.
And yes, I'm going to create a job to add partitions in advance.... :)
A while back I was overloaded with stuff so I set up a year's worth of new monthly partitions in one of our reporting databases. I did not, however, chose to create any nice job to automatically create new partitions or anything like that, as I did not have time (or so I thought).
Well, 'A while back' is now 18 months ago.... Hmm... 6 months of data in one partition. Ouch.
Might not be a big deal with Oracle, but it's SQLServer 2005. Sadly, just splitting 1 day of data into a new partition takes 10 minutes and 9 GB of transaction log space. Very depressing. (At least I tested and timed it before I tried to split one month's data into a new partition).
Kills me some times. Don't put off till tomorrow what you can do today, if tomorrow might become next year... ;)
Hopefully I'll come up with a slick way to split those partitions faster... we'll see.
And yes, I'm going to create a job to add partitions in advance.... :)
Friday, January 18, 2008
MySQL: Innodb internals
Went to the MySQL Performance Tuning Class last week. I would highly recommend it to any DBA new to MySQL, or any MySQL DBA looking to boost their tuning skills and understanding of some of the various features as they relate to performance.
The next few posts will have to do with information I gleaned from that class...
It's always good to understand what's happening underneath the covers when it comes to database performance and evaluating database architectures/designs for new products or features. One of the great things about the innodb engine is it's Oracleish approach to transaction management. The key is Oracle'ish'. As an Oracle DBA looking at MySQL, it's easy to assume that things like undo tablespaces and rollbacks work the same in innodb as they to in Oracle.
Well, you know what happens when we 'Assume'....
One of the key differences is that innodb does not have an undo tablespace or rollback segment per say. Innodb uses the concept of versioned rows, not to unlike SQLServer 2005's concept of versioned rows. SQLServer implements it by storing versions of the row being changed in the tempdb database (though only if the option is turned on). Innodb implements it with storing versions of the row being changed withing the tablespace itself.
Hmm... The obvious implication is fragmentation. As a row gets updated by multiple threads, or a long running transaction, innodb must keep copies of the row with different version numbers (rowid1, version1/rowid 1, version2, etc, etc) within the table so that a select statement has access to that row while the row is potentially changed by some other transaction. This allows for selects not to block updates, but when a transaction is committed, then the versioned rows are marked for delete. This can then cause a high level of fragmentation within the table and/or tablespace.
So, we as DBA's need to consider things like this when we give advice as to what engine should be used for a given application or database table with MySQL as a back end, as well as things like where they should reside on disk, and whether it's ok to use all tables in one tablespace (innodb default), or file_per_table option for innodb to keep fragmentation issues local to the associated tables/indexes. It may also affect the kinds of maintenance and how often it's needed with respect to rebuilds/defrags....
The next few posts will have to do with information I gleaned from that class...
It's always good to understand what's happening underneath the covers when it comes to database performance and evaluating database architectures/designs for new products or features. One of the great things about the innodb engine is it's Oracleish approach to transaction management. The key is Oracle'ish'. As an Oracle DBA looking at MySQL, it's easy to assume that things like undo tablespaces and rollbacks work the same in innodb as they to in Oracle.
Well, you know what happens when we 'Assume'....
One of the key differences is that innodb does not have an undo tablespace or rollback segment per say. Innodb uses the concept of versioned rows, not to unlike SQLServer 2005's concept of versioned rows. SQLServer implements it by storing versions of the row being changed in the tempdb database (though only if the option is turned on). Innodb implements it with storing versions of the row being changed withing the tablespace itself.
Hmm... The obvious implication is fragmentation. As a row gets updated by multiple threads, or a long running transaction, innodb must keep copies of the row with different version numbers (rowid1, version1/rowid 1, version2, etc, etc) within the table so that a select statement has access to that row while the row is potentially changed by some other transaction. This allows for selects not to block updates, but when a transaction is committed, then the versioned rows are marked for delete. This can then cause a high level of fragmentation within the table and/or tablespace.
So, we as DBA's need to consider things like this when we give advice as to what engine should be used for a given application or database table with MySQL as a back end, as well as things like where they should reside on disk, and whether it's ok to use all tables in one tablespace (innodb default), or file_per_table option for innodb to keep fragmentation issues local to the associated tables/indexes. It may also affect the kinds of maintenance and how often it's needed with respect to rebuilds/defrags....
Thursday, December 13, 2007
SQLServer: What am I running anyway?
Microsoft has enough crazy patches and service packs to drive a dba nuts! (that said, Oracle, Mysql, and most other databases have their fair share as well).
Question is though, what version are we running anyway? For those that don't come from the Microsoftie world, the whole daily build number thing in the version is a little out of control.
IE:
Server A:
Besides the obvious X86 vs X64, the only really difference is the nightly build number. They are both actually SP1.
So we should really be checking this way:
Server A:
Question is though, what version are we running anyway? For those that don't come from the Microsoftie world, the whole daily build number thing in the version is a little out of control.
IE:
Server A:
1> SELECT @@VERSIONServer B:
2> go
Microsoft SQL Server 2005 - 9.00.2164.00 (X64)
Jun 14 2006 12:39:07
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
1> SELECT @@VERSIONSo, what is the difference between these two servers?
2> go
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Besides the obvious X86 vs X64, the only really difference is the nightly build number. They are both actually SP1.
So we should really be checking this way:
Server A:
1> SELECT SERVERPROPERTY('productversion'),SERVERPROPERTY('productlevel')Server B:
2> GO
9.00.2047.00 SP1
1> SELECT SERVERPROPERTY('productversion'),SERVERPROPERTY('productlevel')Just another dorky pet peeve of mine :)
2> GO
9.00.2164.00 SP1
Thursday, November 29, 2007
SQLServer 2005: Full Text Issue
In my opinion, there is security, and then there is the absurd paranoia. Microsoft tends to ride this line a little too much lately. Trying to be the end all to security, they forget that we have firewalls, access lists, etc, etc that should be considered when locking things down.
The interesting issue I've run into with Full Text searches now, is , somewhat not surprisingling, a known Microsoft Bug / Feature
The question I'd like to pose Microsoft is this:
If the work break files I've installed came from the CD / Microsoft bits that I got from Microsoft, should I be able to assume that they've verified where they've came from? And for that matter, If I install a third - party work break file after my SQLServer Full Text install, shouldn't it be my responsibility to verify the validity of those files?
Why should Microsoft automatically be checking these for me? (and why, should they do it every time I load the word breaker info into memory?
BTW - if you are having the problem, it presents itself something like this:
The interesting issue I've run into with Full Text searches now, is , somewhat not surprisingling, a known Microsoft Bug / Feature
The question I'd like to pose Microsoft is this:
If the work break files I've installed came from the CD / Microsoft bits that I got from Microsoft, should I be able to assume that they've verified where they've came from? And for that matter, If I install a third - party work break file after my SQLServer Full Text install, shouldn't it be my responsibility to verify the validity of those files?
Why should Microsoft automatically be checking these for me? (and why, should they do it every time I load the word breaker info into memory?
BTW - if you are having the problem, it presents itself something like this:
Running SQLServer 2005 (or possibly other servers), with Full Text Indexing, you notice that the initial call to the query engine is slow (surprisingly, 45 seconds consistently). Subsequent queries perform as expected. Then after 10 or 15 minutes, the query processing engine appears to 'go to sleep' and you have another 45 second response time for a query to indexing service.
Friday, November 16, 2007
SQLServer 2005: Full Text Search Monitoring
Seems more and more lately that a DBA can't get out of being the Full Text query expert. Whether that is an in-database technology such as Full Text/Intermedia Text, or an external database technology such as google appliances / FAST appliances. The typical DBA is always stuck in between somewhere, and looks like we have to understand at least how to monitor and troubleshoot these indexing technologies.
Here are a few important views/selects for monitoring Full Text Indexes withing SQLServer 2005:
Important dm views:
Important system procedures:
Example Queries:
/* Get Status of Full Text Indexes Currently Being Populated */
SELECT
JOIN sys.dm_fts_population_ranges ranges
ON (population.memory_address = ranges.parent_memory_address)
WHERE ranges.session_id <> 0
/* Get List of Full Text Index Population Counts as Compared with Table Counts */
SELECT catalog.name,
CROSS JOIN sys.dm_fts_index_population population
INNER JOIN sys.dm_db_partition_stats tables ON population.table_id = object_id
WHERE catalog.database_id = population.database_id
AND catalog.catalog_id = population.catalog_id
AND catalog.database_id =
Here are a few important views/selects for monitoring Full Text Indexes withing SQLServer 2005:
Important dm views:
sys.dm_fts_active_catalogs /* info about the active ft catalogs */
sys.dm_fts_index_population /* population types for the ft ndexes */
sys.dm_fts_population_ranges /* detailed info about the population of ft indexes */
Important system procedures:
sp_help_fulltext_catalogs /* info on names and locations of ft indexes */sp_help_fulltext_tables /* info on tables and indexes used by ft catalogs */
Example Queries:
/* Get Status of Full Text Indexes Currently Being Populated */
SELECT
DISTINCT DBNAME (database_id),FROM sys.dm_fts_index_population population
OBJECT_NAME (table_id)
JOIN sys.dm_fts_population_ranges ranges
ON (population.memory_address = ranges.parent_memory_address)
WHERE ranges.session_id <> 0
/* Get List of Full Text Index Population Counts as Compared with Table Counts */
SELECT catalog.name,
OBJECT_NAME (population.table_id) AS table_name,FROM sys.dm_fts_active_catalogs catalog
catalog.is_paused,
catalog.status_description,
catalog.row_count_in_thousands AS ft_index_rows_in_thousands,
tables.row_count / 1000 AS table_rows_in_thousands,
population.population_type_description AS population_type,
population.status_description AS status,
population.start_time
CROSS JOIN sys.dm_fts_index_population population
INNER JOIN sys.dm_db_partition_stats tables ON population.table_id = object_id
WHERE catalog.database_id = population.database_id
AND catalog.catalog_id = population.catalog_id
AND catalog.database_id =
(SELECT dbid FROM sys.sysdatabases WHERE name = DB_NAME () )AND tables.index_id = 1;
Monday, October 15, 2007
SQLServer: Image Replication - Oops
Yea, it's always something, huh?
Better mind your default configuration options with SQLServer 2005 if you plan on transactional replication with LOBs. It's nice that they only replicate the appropriate changes, but you'll get an error if you are using images with sizes > the default replication size allowed.
See the Microsoft article for more info.
Fun... :)
Better mind your default configuration options with SQLServer 2005 if you plan on transactional replication with LOBs. It's nice that they only replicate the appropriate changes, but you'll get an error if you are using images with sizes > the default replication size allowed.
See the Microsoft article for more info.
Fun... :)
Monday, October 01, 2007
SQLServer: Transactional Replication Issues
After seeing a few friends at the SQLPass 2007 Summit in Colorado and discussing some of the finer 'features' of pier to pier transactional replication and large database systems (my friends work on the replication team at Microsoft), I quickly ran into one of my big grips with P2P replication and SQLServer 2005.
I had noted that being able to set up P2P from a 3rd party backup tool is essential with large databases (> 100GB) because creating a backup with SQLServer 2005 just takes way too long. The problem is, however, that SQLServer must know which LSN the backup was on when it was restored to the replicated server, so that any transactions that have occurred on the main database since the backup was taken can be correctly applied in the correct order.
The only way for SQLServer to get that is by reading the header in the backup file, which can't be done via a third party backup tool (SQLLightspeed, SQLBackup, etc, etc). My friends implied that if I knew the LSN, it could be done. I'm not quite there yet.
However, it wouldn't be that hard for Microsoft to allow a third party backup tool to call a procedure letting replication know what the current LSN is, thus fixing the problem. Hmm...
Maybe one day. For now, we'll have to deal with 5 hour backups and 2 hours restores in order to setup P2P transactional replication from backup, at least until SQLServer 2008 come around and do the backups for us with it's supposedly blazing fast compression, etc.
I'm not holding my breath....
I had noted that being able to set up P2P from a 3rd party backup tool is essential with large databases (> 100GB) because creating a backup with SQLServer 2005 just takes way too long. The problem is, however, that SQLServer must know which LSN the backup was on when it was restored to the replicated server, so that any transactions that have occurred on the main database since the backup was taken can be correctly applied in the correct order.
The only way for SQLServer to get that is by reading the header in the backup file, which can't be done via a third party backup tool (SQLLightspeed, SQLBackup, etc, etc). My friends implied that if I knew the LSN, it could be done. I'm not quite there yet.
However, it wouldn't be that hard for Microsoft to allow a third party backup tool to call a procedure letting replication know what the current LSN is, thus fixing the problem. Hmm...
Maybe one day. For now, we'll have to deal with 5 hour backups and 2 hours restores in order to setup P2P transactional replication from backup, at least until SQLServer 2008 come around and do the backups for us with it's supposedly blazing fast compression, etc.
I'm not holding my breath....
Friday, September 28, 2007
MySQL Migration Toolkit
I've been evaluating the latest version of the MySQL Migration Toolkit to work on migrations from SQLServer 2005 to MySQL 5.1
Here are some of the things I found so far:
Very Slick....
Here are some of the things I found so far:
- Ease of use:
- Very simple and fast to get up and running - I was able to get multiple databases migrated from SQLServer to MySQL without any special setup or configuration changes. Gui is straight forward and intuitive.
- Took a little work to figure out how to 'customize' the migration though, but nothing crazy.
- Speed:
- Seems relatively fast. I was able to transfer about 6 million rows in about 15 minutes.
- Would probably be more efficient if I wasn't using an intermediary stopping point (my laptop)
- Would benefit if I used bulk loading options, but I wasn't able to determine that right out of the box quickly.
- Issues:
- Needs a 'Migrate Data Only' option for just moving data
- Better options to handle case sensitivity issues (had to modify each table/object manually for that)
- The handling for stored connections was not that great, and I had to keep re-creating the stored connections.
- Should have an option to just do a bulk load from a datafile if necessary
Very Slick....
Wednesday, September 05, 2007
SQLServer: Tracking Down Stat Issues
The old debate of auto update statistics. I've had issues with it in the past, and coming from an old UNIX / Oracle background, I've always been of the opinion that I know best ( probably not, but it's fun to believe that).
The advantage of use sp_updatestats over update statistics is that is looks at sys.sysindexes for the status of indexes and whether or not they need to be updated or not, before the tables/indexes are scanned for updating stats.
Given this, we can then get a look at distribution changes in order to determine how often we should be updating stats on different tables indexes.
The following script gives a look at how many changes have been done since statistics on an object were last updated:
Then just keep track of how fast the rows_updated grows, and use that to get an idea of how often stats should be updated.
The advantage of use sp_updatestats over update statistics is that is looks at sys.sysindexes for the status of indexes and whether or not they need to be updated or not, before the tables/indexes are scanned for updating stats.
Given this, we can then get a look at distribution changes in order to determine how often we should be updating stats on different tables indexes.
The following script gives a look at how many changes have been done since statistics on an object were last updated:
SELECT a.rowcnt AS total_rows,
a.rowmodctr AS rows_updated,
object_name (a.id) AS table_name,
a.name AS stats_object
FROM sys.sysindexes a
WHERE object_name (a.id) IN ( [table name],[table name],...)
ORDER BY 2 DESC,1 DESC
Then just keep track of how fast the rows_updated grows, and use that to get an idea of how often stats should be updated.
Friday, August 03, 2007
SQLServer: Setting up Access from UNIX
These are the detailed instructions for getting t-sql setup to run on UNIX.
This assumes that you have perl 5x installed, and CPAN as well....
#!/usr/bin/perl
#
# test the perl freetds dbi driver
#
use DBI ;
$user = '{dbuser}' ;
$passwd = '{dbpass}' ;
$dbh = DBI->connect('DBI:Sybase:server={servername}',
$user, $passwd);
$dbh->do("use {dbname}");
$action = $dbh->prepare("select count(*) from master..sysprocesses with (nolock)") ;
$action->execute ;
$rows = $action->rows ;
print "rows is $rows\n";
while ( @first = $action->fetchrow_array ) {
foreach $field ( @first ) {
print "$field\t";
}
print "\n";
}
exit(0);
This assumes that you have perl 5x installed, and CPAN as well....
- Update CPAN to the current available version
- cpan > install Bundle::CPAN
- Use CPAN to install the DBI::Bundle
- cpan > install Bundle::DBI
- Install Freetds
- Download files from here
- Gunzip and untar the freetds tar.gz file
- Read the README!
- Run configure tool to customize for your system
- ./configure
- Install freetds
- ./make install
- Add valid SQLServer info to /usr/local/etc/freetds.conf
[{server_connection_name}]- host = {server dns name or ip}
port = {1433}
tds version = 8.0 - Use CPAN to install the DBD::Sybase
- Set SYBASE environment variable to /usr/local
- cpan > force install DBD::Sybase
- Test the install with a perl connection script
#!/usr/bin/perl
#
# test the perl freetds dbi driver
#
use DBI ;
$user = '{dbuser}' ;
$passwd = '{dbpass}' ;
$dbh = DBI->connect('DBI:Sybase:server={servername}',
$user, $passwd);
$dbh->do("use {dbname}");
$action = $dbh->prepare("select count(*) from master..sysprocesses with (nolock)") ;
$action->execute ;
$rows = $action->rows ;
print "rows is $rows\n";
while ( @first = $action->fetchrow_array ) {
foreach $field ( @first ) {
print "$field\t";
}
print "\n";
}
exit(0);
Monday, July 09, 2007
SQLServer: The problem with SQLServer is the OS it runs on :)
Yea,
No matter what way you slice it, the really problem I have with SQLServer, is the operating system it runs on. Seems time after time my SQLServer issues are more related to the OS than anything else.
The latest issue surrounds dual core processors and parallelism.
By default, SQLServer's optimizer seems to look at the number of processors on a systems, multiply it by 2, and use that for the number of parallel threads when running dml in parallel. This was all find and dandy till the OS reported 16 processors on a 8 proc / dual core machine.
Yea, cause then SQLServer says, ohh - I can do this in parallel.... let's see... 16 x 2 = 32 threads! Yea baby!
Uh... great, now my query takes 2x as long because I'm waiting in a line at the door to get in.
Figures.
Take this into account when doing bulk loads, large queries, things that the optimizer might want to parallelize. Either set the max degree of parallelizm at the server level, or use MAXDOP at the query level.
No matter what way you slice it, the really problem I have with SQLServer, is the operating system it runs on. Seems time after time my SQLServer issues are more related to the OS than anything else.
The latest issue surrounds dual core processors and parallelism.
By default, SQLServer's optimizer seems to look at the number of processors on a systems, multiply it by 2, and use that for the number of parallel threads when running dml in parallel. This was all find and dandy till the OS reported 16 processors on a 8 proc / dual core machine.
Yea, cause then SQLServer says, ohh - I can do this in parallel.... let's see... 16 x 2 = 32 threads! Yea baby!
Uh... great, now my query takes 2x as long because I'm waiting in a line at the door to get in.
Figures.
Take this into account when doing bulk loads, large queries, things that the optimizer might want to parallelize. Either set the max degree of parallelizm at the server level, or use MAXDOP at the query level.
Friday, June 22, 2007
SQLServer 2005: Capturing Processes Waiting / Blocked
OK - it's out of order (this is supposed to be a followup on partitioning ), but I'm always writing some code and then later losing it and re-creating it, so I decided to put this up today...
Everyonce in a while we've had short 'blocking / waiting' where things back up in the system. It's kindof a pain in the you know what when it disappears after a few minutes, so I wrote some code to grab all the SQL that's waiting, and stick it in a temp table for later use. Obviously, a working table in a separate database might be more practical, and maybe a few more options on the stored procedures, but for now, here goes:
1. Create a procedure to check for waiting SQL:
Now all you have to do is hope you catch the culprit in action, and that it won't be too difficult to fix, right? :)
Everyonce in a while we've had short 'blocking / waiting' where things back up in the system. It's kindof a pain in the you know what when it disappears after a few minutes, so I wrote some code to grab all the SQL that's waiting, and stick it in a temp table for later use. Obviously, a working table in a separate database might be more practical, and maybe a few more options on the stored procedures, but for now, here goes:
1. Create a procedure to check for waiting SQL:
2. Now Create the Temporary Table:
CREATE PROCEDURE blocked_queries]
@thresh DECIMAL (5,2) = 1.0,
@dbname VARCHAR (128) = NULL
AS
DECLARE
@dbstart INT,
@dbend INT
BEGIN
IF @dbname IS NOT NULL
BEGIN
SET @dbstart = DB_ID (@dbname)
SET @dbend = @dbstart + 1
END
ELSE
BEGIN
SET @dbstart = 4
SET @dbend = 999
END
SELECT DB_NAME (b.database_id) AS [database],
x.host_name AS blocked_host,
a.session_id AS sid,
a.wait_type,
ROUND (CAST (a.wait_duration_ms / 1000.0 AS DECIMAL (5,2)),2,1) as
wait_time_sec,
(
SELECT
SUBSTRING
(text, b.statement_start_offset/2 + 1,
(CASE
WHEN b.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE b.statement_end_offset
END - b.statement_start_offset
) / 2
)
FROM sys.dm_exec_sql_text(b.sql_handle)
) AS blocked_query ,
a.blocking_session_id AS blocking_sid,
(
SELECT
SUBSTRING
(text, c.statement_start_offset/2 + 1,
(CASE
WHEN c.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE c.statement_end_offset
END - c.statement_start_offset
) / 2
)
FROM sys.dm_exec_sql_text(c.sql_handle)
) AS blocking_query
FROM sys.dm_os_waiting_tasks a
join sys.dm_exec_sessions x ON a.session_id = x.session_id
join sys.dm_exec_requests b ON a.session_id = b.session_id
left outer join sys.dm_exec_requests c ON a.blocking_session_id =
c.session_id
WHERE b.database_id >= @dbstart
AND b.database_id < @dbend
AND a.wait_duration_ms / 1000.00 > @thresh
END
create table #blocked_queries3. Last Use the Stored Procedure to Popoulate the Temporary Table:
(
database_name varchar(20),
blocked_host varchar(50),
sid int,
wait_type varchar(20),
wait_time_sec decimal (5,2),
blocked_query varchar(4000),
blocking_sid int,
blocking_query varchar(4000),
lock_time datetime default getdate()
)
declare
@blocked_count int,
@done int
begin
select @blocked_count = count(*) from #blocked_queries
set @done = 10000
while @blocked_count <= @done begin insert into #blocked_queries ( database_name, blocked_host, sid , wait_type, wait_time_sec, blocked_query, blocking_sid, blocking_query ) exec tp_blocked_queries 1.0,end
select @blocked_count = count(*) from #blocked_queries
end
Now all you have to do is hope you catch the culprit in action, and that it won't be too difficult to fix, right? :)
Friday, June 08, 2007
SQLServer 2005: Automated Partitioning Part 1
Partitioning is great, but it has to be automated if you want to do anything regularly with it. And as it typically goes, if you can do a task in repeatable order with T-SQL commands, you ought to be able to automate it, right?
In the next few weeks I'll layout a hopefully straight forward way to automate your partitioning in SQLServer 2005. Though the examples will be done with date range partitioning, the basic methodolgy should be transferable to any types of partitioning.
This week we'll be looking at setting up our partitioning functions, shemes, tables, and initial switching of the current partition(s).
In later weeks' we'll look at automating the merging and/or splitting of partitions as data is rolled out of relevance from a time perspective (IE: X number of months then rolls up into quarters / years as time goes on)
Example: Creating the Partition Function monthly_partition_function
We're going to start with a partition function that partitions by month for one year, then partitions by quarter for one year, then partitions by year. We'll assume that we'll start with 2008-01-01 as our highest defined partition, and 2005-01-01 as our oldest/lowest defined partition.
Next create our partition scheme that lays out partitions onto file groups. For our purposes, we'll put all partitions in the primary file group (In reality, you should put them in different file groups based on your disk subsystem design for performance and managability reasons).
Next create our load table ( example_sales_data_load ) that will be simillarly partitioned according to the rules defined by our partition function and partition scheme in order to insure partition switching will be seamless:
Next auto load our fact table ( example_sales_data) with seed data from previous years and months. We'll load through Feb of 2007 to start with.
Next check that rows were distributed appropriately to table partitions via the sys.dm_db_partition_stats dynamic management object.
Next auto load our load table ( example_sales_data_load) with new data from current month. (This is autogen'd here, but in reality it would be from whatever ETL process).
Next check that rows were distributed appropriately to table partitions via the sys.dm_db_partition_stats dynamic management object. All rows should reside in partition 9 now.
Now we can switch partition 9 of the load table with the main table. Let's auto determine which partition to switch though!
To Manually get the partion number to switch:
OK - It's a lot in one session, but the scripts work, and now you have automated loading of your monthly partitions. Next time we'll look at automating the quarterly partition merges....
In the next few weeks I'll layout a hopefully straight forward way to automate your partitioning in SQLServer 2005. Though the examples will be done with date range partitioning, the basic methodolgy should be transferable to any types of partitioning.
This week we'll be looking at setting up our partitioning functions, shemes, tables, and initial switching of the current partition(s).
In later weeks' we'll look at automating the merging and/or splitting of partitions as data is rolled out of relevance from a time perspective (IE: X number of months then rolls up into quarters / years as time goes on)
Example: Creating the Partition Function monthly_partition_function
We're going to start with a partition function that partitions by month for one year, then partitions by quarter for one year, then partitions by year. We'll assume that we'll start with 2008-01-01 as our highest defined partition, and 2005-01-01 as our oldest/lowest defined partition.
CREATE PARTITION FUNCTION monthly_partition_function (DATETIME)Example: Creating the Partition Scheme monthly_partition_scheme
AS RANGE RIGHT FOR VALUES
(
'2005-01-01', -- partition by year
'2006-01-01', -- partition by year
'2006-04-01', -- partition by q1
'2006-07-01', -- partition by q2
'2006-10-01', -- partition by q3
'2007-01-01', -- partition by q4
'2007-02-01', -- partition by month
'2007-03-01', -- partition by month
'2007-04-01', -- partition by month
'2007-05-01', -- partition by month
'2007-06-01', -- partition by month
'2007-07-01', -- partition by month
'2007-08-01', -- partition by month
'2007-09-01', -- partition by month
'2007-10-01', -- partition by month
'2007-11-01', -- partition by month
'2007-12-01', -- partition by month
'2008-01-01' -- partition by month
)
Next create our partition scheme that lays out partitions onto file groups. For our purposes, we'll put all partitions in the primary file group (In reality, you should put them in different file groups based on your disk subsystem design for performance and managability reasons).
CREATE PARTITION SCHEME monthly_partition_scheme AS PARTITION monthly_partition_function ALL TO ([PRIMARY])Example: Creating the Base Table example_sales_dataNow create our base fact table ( example_sales_data ) that will be partitioned according to the rules defined by our partition function and partition scheme:
CREATE TABLE example_sales_data ( trans_date DATETIME NOT NULL, trans_amt DECIMAL (5,2) NOT NULL, trans_type INT, sales_id INT, store_id INT, cust_id INT ) ON monthly_partition_scheme (trans_date)Example: Creating the Load Table example_sales_data_load
Next create our load table ( example_sales_data_load ) that will be simillarly partitioned according to the rules defined by our partition function and partition scheme in order to insure partition switching will be seamless:
CREATE TABLE example_sales_data_load ( trans_date DATETIME NOT NULL, trans_amt DECIMAL (5,2) NOT NULL, trans_type INT, sales_id INT, store_id INT, cust_id INT ) ON monthly_partition_scheme (trans_date)Example: Auto Load Seed Data Into Table example_sales_data
Next auto load our fact table ( example_sales_data) with seed data from previous years and months. We'll load through Feb of 2007 to start with.
DECLAREExample: Verify Seed Data Via Partition Counts example_sales_data
@my_trans_date DATETIME ,
@my_trans_amt DECIMAL (5,2),
@my_trans_type INT,
@my_sales_id INT,
@my_store_id INT,
@my_cust_id INT,
@my_cur_date DATETIME
BEGIN
SET @my_cur_date = GETDATE() - 1200
SET @my_trans_date = @my_cur_date
SET @my_trans_amt = 10.00
SET @my_trans_type = 1
SET @my_sales_id = 1
SET @my_store_id = 1
SET @my_cust_id = 1
WHILE CAST ('2007-03-01' AS DATETIME) > @my_cur_date
BEGIN
WHILE @my_trans_type <= 3
BEGIN
WHILE @my_sales_id <= 5
BEGIN
WHILE @my_store_id <= 6
BEGIN
WHILE @my_cust_id <= 4
BEGIN
WHILE @my_trans_amt <= 500.00
BEGIN
INSERT INTO example_sales_data
VALUES
(
@my_trans_date,
@my_trans_amt,
@my_trans_type,
@my_sales_id,
@my_store_id,
@my_cust_id
)
SET @my_trans_amt = @my_trans_amt * 1.25
END -- @my_trans_amt
SET @my_trans_amt = @my_trans_amt / 10.0
SET @my_cust_id = @my_cust_id + 1
END -- @my_cust_id
SET @my_cust_id = 1
SET @my_store_id = @my_store_id + 1
END -- @my_store_id
SET @my_store_id = 1
SET @my_sales_id = @my_sales_id + 1
END -- @my_sales_id
SET @my_sales_id = 1
SET @my_trans_type = @my_trans_type + 1
END --@my_trans_type
SET @my_trans_type = 1
SET @my_cur_date = @my_cur_date + 7
SET @my_trans_date = @my_cur_date
END --@my_cur_date
END -- Base Table Load
Next check that rows were distributed appropriately to table partitions via the sys.dm_db_partition_stats dynamic management object.
SELECT OBJECT_NAME ([object_id]),partition_number,row_countExample: Auto Load New Data Into Load Table example_sales_data
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('example_sales_data')
Next auto load our load table ( example_sales_data_load) with new data from current month. (This is autogen'd here, but in reality it would be from whatever ETL process).
DECLAREExample: Verify Seed Data Via Partition Counts example_sales_data_load
@my_trans_date DATETIME ,
@my_trans_amt DECIMAL (5,2),
@my_trans_type INT,
@my_sales_id INT,
@my_store_id INT,
@my_cust_id INT,
@my_cur_date DATETIME
BEGIN
SET @my_cur_date = CAST ('2007-03-01' AS DATETIME)
SET @my_trans_date = @my_cur_date
SET @my_trans_amt = 10.00
SET @my_trans_type = 1
SET @my_sales_id = 1
SET @my_store_id = 1
SET @my_cust_id = 1
WHILE CAST ('2007-04-01' AS DATETIME) > @my_cur_date
BEGIN
WHILE @my_trans_type <= 3
BEGIN
WHILE @my_sales_id <= 5
BEGIN
WHILE @my_store_id <= 6
BEGIN
WHILE @my_cust_id <= 4
BEGIN
WHILE @my_trans_amt <= 500.00
BEGIN
INSERT INTO example_sales_data_load
VALUES
(
@my_trans_date,
@my_trans_amt,
@my_trans_type,
@my_sales_id,
@my_store_id,
@my_cust_id
)
SET @my_trans_amt = @my_trans_amt * 1.25
END -- @my_trans_amt
SET @my_trans_amt = @my_trans_amt / 10.0
SET @my_cust_id = @my_cust_id + 1
END -- @my_cust_id
SET @my_cust_id = 1
SET @my_store_id = @my_store_id + 1
END -- @my_store_id
SET @my_store_id = 1
SET @my_sales_id = @my_sales_id + 1
END -- @my_sales_id
SET @my_sales_id = 1
SET @my_trans_type = @my_trans_type + 1
END --@my_trans_type
SET @my_trans_type = 1
SET @my_cur_date = @my_cur_date + 7
SET @my_trans_date = @my_cur_date
END --@my_cur_date
END -- Base Table Load
Next check that rows were distributed appropriately to table partitions via the sys.dm_db_partition_stats dynamic management object. All rows should reside in partition 9 now.
SELECT OBJECT_NAME ([object_id]),partition_number,row_countExample: Switch partition from LOAD table to MAIN table
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('example_sales_data')
Now we can switch partition 9 of the load table with the main table. Let's auto determine which partition to switch though!
To Manually get the partion number to switch:
SELECT $PARTITION.monthly_partition_function ( (To Auto Generate the partion number and switch:
SELECT TOP 1 (trans_date) FROM example_sales_data_load ) )
DECLARE
@switch_str VARCHAR (512),
@target_partition INT
BEGIN SELECT @target_partition = $PARTITION.monthly_partition_function
((
SELECT TOP 1 (trans_date)
FROM example_sales_data_load
))
SET @switch_str = 'ALTER TABLE example_sales_data_load '
SET @switch_str = @switch_str + 'SWITCH PARTITION ('
SET @switch_str = @switch_str + CAST (@target_partition AS VARCHAR)
SET @switch_str = @switch_str + ') TO example_sales_data'
SET @switch_str = @switch_str + ' PARTITION ('
SET @switch_str = @switch_str + CAST (@target_partition AS VARCHAR)
SET @switch_str = @switch_str + ')'
EXEC (@switch_str);
END
OK - It's a lot in one session, but the scripts work, and now you have automated loading of your monthly partitions. Next time we'll look at automating the quarterly partition merges....
Friday, May 18, 2007
VMWare: The greatest thing since sliced bread
So if you like to try out new things, and you're not using VMWare yet, get with the program!
Really, though - it's so easy now (and the VMServer is free) . Just download and install.
VMWare lets you run mini virtualized server environments within your current operating system. Yea, there is a little overhead, but for testing and trying out new databases, configurations, applications, it's really cool.
The best part of it all, is the fact that you can download a host of pre-built applications to run with in the VMServer you just installed. Just startup the server and, wala... a running MYSql database in slackware on your Dell windows laptop. Yea, they have an Oracle 10g RAC appliance, as well as a SQLServer 2005 appliance.
Sweet. If you haven't checked it out, do so now...
Really, though - it's so easy now (and the VMServer is free) . Just download and install.
VMWare lets you run mini virtualized server environments within your current operating system. Yea, there is a little overhead, but for testing and trying out new databases, configurations, applications, it's really cool.
The best part of it all, is the fact that you can download a host of pre-built applications to run with in the VMServer you just installed. Just startup the server and, wala... a running MYSql database in slackware on your Dell windows laptop. Yea, they have an Oracle 10g RAC appliance, as well as a SQLServer 2005 appliance.
Sweet. If you haven't checked it out, do so now...
Monday, May 14, 2007
SQLServer 2005: DMV's and determiniing fragmentation
Dynamic Management Views (which are not all views, some are stored procedures / functions) are very useful and if you're a DBA working with SQLServer, you should know them inside and out, and have you're list of scripts ready at hand.
Here's a couple that work well for determinining fragmentaion issues:
*Note: Though these can be done on line, the due use CPU and heavy READ I/O - be sure to know your application and how that affects performance, and carfully track performance while they are running the first few times. (Use the DMV options to control how much I/O is done - in the example we use the 'LIMITED' option)
Here's a couple that work well for determinining fragmentaion issues:
*Note: Though these can be done on line, the due use CPU and heavy READ I/O - be sure to know your application and how that affects performance, and carfully track performance while they are running the first few times. (Use the DMV options to control how much I/O is done - in the example we use the 'LIMITED' option)
SELECT substring (b.name,1,40) + '.' +
substring (c.name,1,50) AS Object,
a.avg_fragmentation_in_percent AS fragmentation_pct
FROM sys.dm_db_index_physical_stats
(
db_id ('{DBNAME}'),
object_id ('{TABLE/OBJECT NAME}'),
NULL,
NULL,
'LIMITED'
) a
JOIN sysobjects b
ON a.object_id = b.id
LEFT OUTER JOIN sysindexes c
ON a.object_id = c.id
AND a.index_id = c.indid
Tuesday, March 13, 2007
SQLServer 2005: Working with partitions
Now that SQLServer finally supports something the database world would really consider a decent first attempt at partitioning, I think it's high time we all start putting it to practice.
Partitioning in the Oracle world is almost taken for granted. It's one of the most straight forward ways to scale a database in terms of size and performance, without looking at additional hardware and/or heavy software changes in terms of data flows and use patterns.
Partitioning does not need to be restricted to data warehousing either. It has very practical applications to OLTP databases. When it comes to SQLServer, in my mind, it has even more impact on performance than it does with Oracle because of SQLServer's implementation of locking (although, technically, with SQLServer 2005, it is possible to implement a reader's don't block writer's transaction scheme).
This is because partitioning allows us to have more control over what rows/pages get locked during inserts and selects if we design it correctly. The next few posts with focus on ways to partition an OLTP application, and my workarounds for unimplemented schemes like hash-partitioning. Most of my initial on the job training I give credit to Frank Bommarito, an incredibly talented Oracle architect.
Partitioning in the Oracle world is almost taken for granted. It's one of the most straight forward ways to scale a database in terms of size and performance, without looking at additional hardware and/or heavy software changes in terms of data flows and use patterns.
Partitioning does not need to be restricted to data warehousing either. It has very practical applications to OLTP databases. When it comes to SQLServer, in my mind, it has even more impact on performance than it does with Oracle because of SQLServer's implementation of locking (although, technically, with SQLServer 2005, it is possible to implement a reader's don't block writer's transaction scheme).
This is because partitioning allows us to have more control over what rows/pages get locked during inserts and selects if we design it correctly. The next few posts with focus on ways to partition an OLTP application, and my workarounds for unimplemented schemes like hash-partitioning. Most of my initial on the job training I give credit to Frank Bommarito, an incredibly talented Oracle architect.
Wednesday, May 17, 2006
SQLServer: 2005 Interface Woes
We've been evaluating SQLServer 2005 for a couple weeks now. It's taken that long just to get used to the new interface. In my humble opinion, it would have been better to spend more time working on features than working on the interface.
That said, after a few weeks of playing with it, I'm getting used to the new interface.
Drawbacks:
That said, after a few weeks of playing with it, I'm getting used to the new interface.
Drawbacks:
- Very resource consuming on the desktop
- Bringing up the properties / options page is very slow
- Getting a new query page is very slow as well
- The dynamic help tab is a nice addition as it doesn't' pop up a new window, and comes up fast
- Tabbed query pages/connections are great for organization purposes if you are working on multiple databases/servers at the same time. Reminds me of why I use Firefox instead of Internet Explorer.
- I suppose if you are a Visual Studio programmer, the interface is probably very straight forward for you.
Subscribe to:
Posts (Atom)