Next week I’ll be stepping into the lion’s den of sorts. I’ll be off to Las Vegas to attend EMC World. I went last year, and I’m pretty sure that I was about the only full time DBA there. The storage folks treated me pretty nice, even after they found out that I was a DBA.
Hopefully when I get back I’ll have all sorts of new storage tips and tricks to share with everyone.
If anyone else if brave enough to attend let me know here. Maybe we can meet up at one of the events.
Back in the old days one of the big reasons that people enforced such strict rules on data normalization was because it would greatly increase the amount of information you could fit on the hard drive. Back when everything was measured in kilobytes or megabytes storing redundant information simply wasn’t an option. You had to be able to cram as much information into that little database.
These days space isn’t the issue. Storage is very cheep these days, and if you have the money you can get absolutely massive amounts of storage. 1 TB hard drives are easily found, and when you start talking about RAIDing drives together the storage limits start to become just insane. 100 TB of storage just isn’t what it used to be.
The problem now becomes that with so much data for the database platform to search through we need to make sure that the indexes which SQL Server is searching as as small as possible so that we can get those indexes read from the disk and into memory as quickly as possible.
Say we have an Employee table which looks like this.
CREATE TABLE Employee
Now this table is clearly not setup as a normalized table. We are assuming that this table has best case indexing. If we have 1000 employees records and we are going to search the Department column the index must be read from disk. Now assuming that the department names are nice long bureaucratic names which average 30 characters each we have about 30000 bytes (more if you take the fill factor into account) which needs to be read from the disk. Now if we had a relational version of the table.
When we search the Employee table we now are only loading an integer value from the disk into memory. This means that we only have to load 8000 bytes of data from the disk into memory. Now knowing that SQL Server reads data from the disk in 8k blocks even with a 50% fill factor the index only takes 2 blocks on the disk and can then be completed in two read operations to the disk. Now our original table with no fill factor will require 30 data pages (20.297 to be specific), about 60 if we use the same fill factor.
With tables of this size this isn’t much. But as your database grows the number of additional data pages which would have to be read for a simple search of the table grows exponentially.
The SQL Server Service Broker is a fantastic new addition to SQL Server. For those who have used Microsoft Message Queue (MSMQ) the service broker will be easy to understand as it’s the same basic concept. Messages are sent from one place to another, within a database, from database to database, or from server to server.
While the configuration can be a daunting task, once the service broker is setup it is a very solid system which can handle a large message load.
The Service Broker give you guaranteed delivery order, with single processing of messages always in the order received. Messages can be processed on demand (you write software which queries the service broker on a schedule) or automatically via the queue activation.
Check back for information about all the various service broker object an how to configure each of them.
As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.
And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.