Best way to get assistance on the messages boards.

There are literally thousands of different message boards on the Internet where you can find technical information from fellow IT professionals.  When asking questions on these boards there are some basic guidelines which most people like to have followed.  They are sometimes informal, but they are fairly common across most of the boards.

  1. Don’t use topics like “Help!!!” or “It doesn’t work”.  Using a descriptive topic will make more people want to read your thread and help you out.
  2. Be polite when talking to the people who are trying to help you.  They aren’t getting paid to do this, so be nice to them.  It’s always nice to get a thank you from the poster.
  3. Check your spelling and grammar.  It doesn’t have to be perfect, but many people prefer proper spelling and grammar. (Mine isn’t the best, but I do try.)  If English isn’t your first language and people are giving you a hard time about your spelling or grammar some people will be a little more forgiving once they know that you are not a native English speaker.
  4. If the message board is in English and you are posting an error message which is in another language, please translate it to English.  More people will be able to read it that way.  You may not get the translation perfect, but it’s usually close enough to get the point across.
  5. Don’t post just the error number.  There are thousands of error messages and we need the entire error message.
  6. Don’t just say that it’s broken.  Be specific about what isn’t working.
  7. Let the readers know what you have done to try to fix the problem already.  That will save you time as the people trying to help won’t have to have you try stuff twice.
  8. If you find the solution your self post it to the thread.  This will help the next person who has the same problem.  These forums are all about helping others and this is a great way to give back to the community.
  9. Please remember that the people responding aren’t computers.  It may take time for someone to read your thread who knows the answer.

Denny

Contact the Author | Contact DCAC

What’s up with the N in front of string values?

If you have looked at pretty much any Microsoft provided T/SQL code you have probably seen an N in front of a string value when setting a variable much like this.

SELECT *
FROM sys.databases
WHERE name = N'master'

What this N does is tell the SQL Server that the data which is being passed in is uni-code and not character data.  When using only the Latin character set this is not really needed.  However if using characters which are not part of the basic Latin character set then the N is needed so that SQL knows that the data being given it is uni-code data.

Denny

Contact the Author | Contact DCAC

What is Service Broker?

Service Broker is a transaction message queueing system build into Microsoft SQL Server.  It provides you with in order, guaranteed single read, message processing that is handled and managed with T/SQL code.  This makes it extremely easy to send and process messages either within a single database, or send those messages to a remote database on another server for processing.  Messages are sent as XML documents so a message payload can contain a single field of data or a multiple row record set as a single message.

For those familiar with Microsoft Message Queue you will find that Service Broker is very similar to MSMQ but is native to the Microsoft SQL Server.

Server Broker can route messages from database to database, or server to server.  Messages can be processed or routed to another server for processing there.  Queues can be setup to hold messages for processing by an application or job, or have the messages processed as soon as they arrive by an activation procedure which is simply a procedure which is fired as messages arrive.  Activation procedures can be run as a single thread or several threads pulling from the same queue at once.

 Look for a future blog posting on configuring and using the service broker to send and process messages.

Contact the Author | Contact DCAC

Getting the right disk config for your SQL Server

Many DBAs so not have the knowledge needed in the storage area to be able to ask the right questions to there storage engineers, and often storage engineers do not know the right questions to ask a DBA to get the answers that they need.  Because of this database storage ends up being configured correctly for a file server, but not for a database.  This can lead to slow response time within the database, which can lead to blocking and eventually timeouts on the front end.

Correctly configured and sized storage is essential to running a database at peek performance.  Unfortunately when configuring storage even the smallest misconfiguration can be detrimental to the entire database and it’s front end application.

As some initial reading please take a look at this tip I wrote for SearchSQLServer.com called Optimize disk configuration in SQL Server.  There is also an excellent tip by Hilary Cotter called SAN considerations for your SQL Server environment.

Today I’ll be focusing on databases which use SAN storage, but some of it will still apply to databases using local storage.

When configuring your storage be sure to look beyond just the storage capacity of the drives.  You also need to consider the Input/Output operations (IOs) that each drive and array can support.  If you have enough space, but not enough IOs your database will not work at the speed you need.  With drive sizes growing as quickly as they are, it is very easy to get into trouble and not have enough IO capacity.  If you have a database which needs 500 Gigs of Storage, you could fit that database onto a single 750 Gig drive.  Would that hard drive have enough speed to handle the IO requirements of your application, probably not.

Unfortunately there is no good tool available to help you figure out how many IOs you will need in order to support your database.  I recommend planning high, but not crazy high.  If you are expecting 10000 transactions per hour you do not need to be able to support 10 million transactions per hour, however the number of transactions and IOs which are needed will change over time as the business grows.  Because of this it is recommended to routinely evaluate the IO requirements for the database and ensure that you can meet them.  If not, it may be time for an upgrade.  Before you go requesting budget for an upgrade do not forget to tune your indexes.  As tables grow index and statistic usage can change and may need to be reviewed before making changes to the storage.

While there is a correlation between transactions and IOs, there is no direct formula from one to the other.  Some transactions may work completely from the buffer cache and cause no transactions, while some may cause tens of thousands of reads while exporting data for loading into your data warehouse.

When working in a SAN environment be sure to take into account not just the requirements of your database, but also of the other LUNs which will be sharing your RAID group.  A classic example is where there is a RAID 0+1 RAID group made up of 4 disks, each with the potential of handling 120 IOs per second for a total of 240 IOs per second.  There are three databases which need storage on this RAID Group.  Each has a requirement of 100 IOs per second.  But because they are created at different times the IO requirements aren’t being tracked very well.  After the second LUN is created everything should still continue to work fine.  When the SAN administrator gets in his request for a third LUN to be created he looks at his RAID group and sees that there is plenty of space on the RAID Group.  With no IO information from the DBA he doesn’t know that this LUN will overload the RAID Group and cause a slowdown of all three LUNs.  Because the DBA doesn’t know that the three databases are all on the same RAID group he doesn’t know that there will be a problem either.  But as soon as the third database goes into production and starts getting a load all three applications start to feel the slowdown.

 Because the proper questions were not asked by either the DBA or the SAN administrator we now have applications which are not behaving correctly.

One question that I am often asked is “How many IOs can a disk actually handle?”.  Unfortunately there is no good answer to that question.  Each drive vendor will be slightly different.  Something which will also effect the answer to this question is what is called the IO profile of the drive.  Sequential IO operations (reading Sector 1,  then Sector 2, then Sector 3, etc) is much faster than random IO operations (reading Sector 158, then Sector 21, then Sector 851, etc).  Unfortunately most database traffic is random by nature.  OLTP applications involve systems accessing small bits of the database all over the physical file.  SQL will try to correct for this by caching as much data as it can into memory so that it doesn’t have to go to the disk very often, but most disk access will still be very random in nature.  Operations such as OLAP data loads and OLTP table scans are typically sequential, providing that your tables are laid out correctly in separate file groups.

There are other things which can effect the randomness of your disk access such as table scans, low buffer cache hit ratios, data file location, page file location, other applications using SQL Servers disks, etc.

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

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.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers