Webcast: Do It Right: Best Practices for Analysis Services 2005 and 2008 – Today at 12:00 Pacific
Are you seeking practical, in-depth technical advice for building a BI solution using SSAS? Analysis Services changed tremendously with the advent of SQL Server 2005. It introduced a new way of building dimensions and cubes that required a new way of modeling the solution. This session examines the best practices for properly designing cubes for performance and usability. It discusses some high-level topics but also looks at advanced topics such as alternative approaches to many-to-many dimensions, SCOPE statements, aggregation design, scalability issues, processing techniques, server properties, and more. Craig Utley has been working with Microsoft’s BI products for 11 years and is a former Program Manager with the SQL Server Customer Advisory Team (SQLCAT), where he worked with some of the largest Analysis Services installations worldwide. In this session he brings his real-world experience with large, complex SSAS projects and presents best practices uncovered as companies deploy and use Analysis Services.
Presented by: Craig Utley
> Register Now
> Live date: 12/8/2010 at 12:00 Pacific
RedGate SQL Server Tools
RedGate says "SQL Toolbelt is the simplest and most effective way to access the SQL Server tools that you need, to get your job done, the way you want to do it. SQL Toolbelt is a single installer of fourteen essential database tools, including SQL Compare, SQL Prompt and SQL Backup, and is the industry standard in the world’s leading companies, including Microsoft, HP, Sage, Bank of America, AT&T and the US Treasury."
The thing I appreciate most about this suite of tools is the broad range of extensions to SQL Server making the job of a Database Administrator manageable. The monitoring capabilities are first rate without adding stress to your database servers. Get a baseline of how your server is performing; then you can see and predict changes that impact the performance of your servers.
Equally valuable are the alerts built into the tools notifying you when issues exceed allowed values. You can even add your own customized alerts.
Do you travel? Are your servers hosted off site? Web based monitoring provides access when you are not local to your servers.
These are just highlights…there is so much RedGate SQL Server tools can do for you. Go check ’em out.
Featured Article(s)
SQL Server 2008 Profiler Optimization Tips
Here are some helpful tips to performance tune and optimize SQL Server 2008 Profiler.
Are Stored Procedures the Only Way to Code SQL?
No. A programming shop with the right discipline for using embedded SQL will experience the same benefit as a shop that uses stored procedures exclusively.
Now I am going to drop the hammer and rant a little bit. I spent the last week inline SQL code in a number of applications because one of my production SQL Servers was consistently running out of memory and experiencing a great number of faults (pushing memory in and out of the swap file).
A 64 bit version of SQL Server experiences this more than a 32 bit. What was the cause? Inline SQL that was not in a shared layer of the application (arghh, embedded throughout aspx code). OK, bad design to start with. Worse yet, there was a lot of duplication. Most of the queries were parameterized to address SQL Injection. However, I found a number that were not, but the query was altered for different filtering and sorting conditions. It’s just easier to write the code that way.
Here is the rub. When SQL Server receives an SQL query it parses it and searches cache for a query plan that is an EXACT match. Even if your instance of SQL Server is not case specific, the optimizer requires inline SQL to be exactly the same in Case, spacing, line feeds, and parameters. If anything is different, that becomes a new query. Yes, there is a cached plan just like a stored procedure. But, every request has to be tokenized to be able to compare the query plan cache.
When you embed code all over the place it is quite likely you may change the slightest thing resulting in a new query plan. When you don’t use parameters for inline SQL you also experience a new plan. We had queries where the SQL String was built and the value to be sought was concatenated to the SQL since it wasn’t provided by users and was not susceptible to SQL Injection. For example:
SELECT * FROM OrderDetails WHERE OrderMasterID = 23
This plan will only work for OrderMasterID = 23. Any other number results in another plan. This functions very much like a memory leak.
Replacing 23 with a parameter, even using inline SQL makes the query plan re-usable.
SqlCommand CMD = new SqlCommand();
CMD.CommandText = "SELECT * FROM OrderDetails WHERE OrderMasterID = @OrderMasterID";
CMD.Parameters.AddWithValue(@OrderMasterID, 23);
If you simply made this a stored procedure, you would not experience this problem, even with a bad architecture. Here’s my list for today:
- Depending on your database configuration, Stored Procedures accessed through ADO are not case specific This is the default.
- You must always use Parameters for replaceable values resulting in a non-negotiable re-usability
- Procedure parameters are Type Specific resulting in reduced errors from late type casting failures.
- Inline Query Plans are removed or swapped from cache earlier than stored procedures. When your server becomes stressed for memory, Stored Procedures remain in cache longer than inline query plans.
- There is a slight, non-measurable performance boost in parsing a Stored Procedure compared to an Inline Query unless you have a specified batch
- Stored Procedures are always centralized in one location, regardless of your application architecture
I’m not saying stored procedures are the only way to go. If you don’t use them, be sure to practice good application development best practices.
I know this is an old battle with strong feelings for and against. So be sure and drop me your comments, especially if you don’t agree, or would stress something differently.
Cheers,
Ben