Data access optimization steps in SQL Server

Use Indexing

• Apply proper indexing in the table columns in the database
• Make sure that every table in your database has a primary key.
• Create non-clustered indexes on columns which are, frequently used in the search criteria, Used to join other tables, Used as foreign key fields , Of having high selectivity (Column which returns a low percentage (0-5%) of rows from a total number of rows on a particular value) or Used in the ORDER BY clause
  
• Create appropriate covering indexes
• Use Database Tuning Advisor’s help while creating covered index
  
• Defragment indexes if fragmentation occurs

It's really tempting to create index on all eligible columns in your database tables. But, if you are working with a transactional database (An OLTP system where update operations take place most of the times), creating indexes on all eligible columns might not be desirable every time. In fact, creating heavy indexing on OLTP systems might reduce overall database performance (As most operations are update operations, updating data means updating indexes as well).

A rule of thumb can be suggested as follows: If you work on a transactional database, you should not create more than 5 indexes on the tables on an average. On the other hand, if you work on a Data warehouse application, you should be able to create up to 10 indexes on the tables on an average.

Re-factor TSQLs and apply best practices

• Move TSQL codes from application into the database server
• Identify inefficient TSQLs, re-factor and apply best practices
• Don’t use “SELECT*" in SQL Query
• Avoid unnecessary columns in SELECT list and unnecessary tables in join conditions
• Do not use the COUNT() aggregate in a subquery to do an existence check
• Try to avoid joining between two types of columns
• Try to avoid deadlocks 
• Write TSQLs using “Set based approach” rather than using “Procedural approach” 
• Try not to use COUNT(*) to obtain the record count in the table 
• Try to avoid dynamic SQLs
• Try to avoid the use of Temporary Tables, try to use Table variables.
• Instead of LIKE search, Use Full Text Search for searching textual data 
• Try to use UNION to implement “OR” operation 
• Use VARCHAR(MAX), VARBINARY(MAX) and NVARCHAR(MAX) 
• Do not call functions repeatedly within your stored procedures, triggers, functions and batches.
• Try to avoid the use of triggers.
• Try to avoid nested transaction.
• Understanding the query execution plan
• Getting rid of the unnecessary Text column in the SELECT list
  

Apply advanced indexing and denormalization

• Apply some advanced indexing techniques
• Implement computed columns and create index on these
• Create "Indexed Views"
• Create indexes on User Defined Functions (UDF), using computed columns
• Create indexes on XML columns
  
• Apply de-normalizations, use history tables and pre-calculated columns

Diagnose database performance problems

• Diagnose performance problem, use SQL Profiler and Performance Monitoring tool effectively.
  

Optimize database files and apply partitioning

• Organize the file groups and files in the database
• Apply partitioning in the big fat tables
  

Original Source: M.M.Al-Farooque Shubho

YouTube Scalability

This talk will discuss some of the scalability challenges that have arisen during YouTube's short but extraordinary history. YouTube has grown incredibly rapidly despite having had only a handful of people responsible for scaling the site. Topics of discussion will include hardware scalability, software scalability, and database scalability.

Speaker: Cuong Do Cuong is currently an engineering manager at YouTube/Google. He was part of the engineering team that scaled the YouTube software and hardware infrastructure from its infancy to its current scale. Prior to YouTube/Google, he held various software development and management positions at PayPal and Inktomi.



Google Tech Talks June 23, 2007

Optimize your Web site performance to survive heavy traffic

1. Avoid accessing databases to often
On a database driven content sites, the slowest task is accessing the database to retrieve the content to display. If a site is not fast enough to serve its pages, many simultaneous user accesses force the Web server to create more processes to handle all the requests.
This is bad because it demands more server memory. Once the server RAM is exhausted, the machine starts using the virtual memory, making the server even slower, until it halts completely.

2. Cache Web pages
If the site needs to access the database to retrieve the content to display, what can we do to minimize the database accesses?
First, we need to focus on what kind of information the site retrieves from databases. The most common type of data is information used to build the HTML pages.
The fact is that the information in database does not change so frequently. As a matter of fact, usually different users see the exact same HTML content when they access the same page.
It is not unusual to execute many SQL queries to retrieve all the data that is necessary to build a single HTML page. So, it is evident that it would be much more efficient if the sites could cache the HTML of each page after it is accessed for the first time.
So, what if the database content changes? Just call a code that invalidates all the page caches that depend on content of the changed database table rows. This way, it forces the caches to be rebuilt on the next access to the site pages, and so the pages are always upto date.

3. Avoid needless personalization
What about pages that appear differently to each user that accesses them? Use separate cache files depending on the user accessing the pages.
However, it would useless if the site would use a separate cache file to store the HTML that each user sees. The benefit of reusing cached information would be lost.
To maximize the efficiency of this approach you should minimize the number of user profiles that may be used for each page context. Therefore, it is very important to avoid personalization as much as you can.

4. Queue tasks that may take too long
Caching is great to avoid repeated accesses to the same content stored in a database.
However, caching only applies to accesses that retrieve data from databases. Operations that update the database content may not benefit from caching. However, when done to frequently, database write accesses may cause server overload.
One of the solution is not to update that table in real time. Instead, create a similar table that act as a queue. The queue table has no indexes. Periodically, start a background task from cron, and move the queue table data to the main table.

5. YSlow 13 optimization rules
There are many aspects to be concerned regarding what to do to make browsers interact with the Web servers in an optimized way that it takes less time and less server resources.
The issues are often not trivial to understand for people looking for quick and easy solutions to optimize their Web sites. Fortunately, there are tools that help you to audit your sites and suggest what needs to be done to boost the performance of the sites to the limit.

YSlow is one of those Web site performance auditing tools. This is a tool that the fine folks of the team of Jeremy Zawodny at Yahoo! released a few weeks ago.
http://developer.yahoo.com/yslow/

This is a wonderful tool that in a few seconds gives you an overview of how a page of your site is being served. It also suggests what can be done to optimize aspects that affect page loading speed and the consumption of Web server resources.
This tool is an extension for the Firefox browser. It works together with another very good extension named Firebug. So, to use YSlow, first you need to install Firebug.
http://www.getfirebug.com/

Once you have installed Firebug and YSlow, it is very easy to audit the performance of any Web page. Just load a Web page that you want to test and wait until it finishes loading. Then open the Firebug pane, click on the YSlow tab, and use the Performance button.

Immediately YSlow starts collecting details about the current page. When it is done it shows a list of 13 rules about aspects of the page loading performance.
On the left side of the listing you see grades from A to F. Those grades express how your page performs on each of the 13 rules, A being the best and F being the worst. On the top of the listing you see your overall "Performance Grade" also from A to F, and a score between 0 and 100.
If you got the score 100, congratulations, your page is perfect. Otherwise there is performance tuning work to be done.

Thirteen Simple Rules for Speeding Up Your Web Site are

1. Make Fewer HTTP Requests
2. Use a Content Delivery Network
3. Add an Expires Header
4. Gzip Components
5. Put Stylesheets at the Top
6. Put Scripts at the Bottom
7. Avoid CSS Expressions
8. Make JavaScript and CSS External
9. Reduce DNS Lookups
10. Minify JavaScript
11. Avoid Redirects
12. Remove Duplicate Scripts
13. Configure ETags

6. Too much AJAX and external Javascript may kill your page performance
Using AJAX and cool Javascript libraries is definitely the latest fashion in Web application development. These are common aspects that you may notice in the so-called Web 2.0 sites.
The problem is that when you use a Javascript library that has may interdependent components, sometimes to use a simple component you end up loading a pile of separate Javascript files.

This makes the browser send a lot of requests to the Web server once it enters a page that needs many of those Javascript files. Not only it may cause excessive load to the server, but it also slows down the rendering of a page, even when the page HTML has already been fully loaded. It is a similar effect of pages that use Flash movies that take a while to load.

Browsers cache Javascript files, but when the user is making the first access to a page that needs many Javascript files, it may take too long. It may give the impression that your site is much slower than it actually is.

More detailed information regarding rules to optimize your Web site performance and for defensive programming practices to survive Web site traffic peaks can be found in www.phpclasses.org

A Fix() Function in T-SQL

Someone today asked me for a SQL Server function that would round a number to the first two significant places. The idea would be that 0.243 would round to 0.24, 0.00592 would round to 0.0059, and 34600 would round to 35000.

Like all good developers, I start with creating some tests. :)

I'm not using a proper testing tool here, this is just for fun - so I'm going to just make a resultset with two columns - the one that I should get, and the one that I expect to get. And I'll throw in a couple of extra numbers, to make sure I cater for the negative cases as well.

select * from
(         select 0.243 param, dbo.fix(0.243) result, 0.24 wanted
union all select 0.00592, dbo.fix(0.00592), 0.0059
union all select 34600, dbo.fix(34600), 35000
union all select -3323, dbo.fix(-3323), -3300
union all select -3.59, dbo.fix(-3.59), -3.6
) t
where t.result <> t.wanted 

Obviously this won't work yet, I don't have a function called fix (which I'm naming for the comparison with the 'fix' function in other systems). So let's create it:

create function dbo.fix(@num float) returns float as
begin
return (@num)
end

I'm using float because I'm lazy. I could use numeric, but float is quick to type, and does the job for now.

Right. Now I run my test, and all five results come back. Lovely. So now let's fix the function.

So how do we do this... well, the round() function in SQL Server will round a number nicely. It takes a parameter which is the number of decimal places you want. And it takes negative numbers. round(12345,-3) gives 12000. That's just what I'm after. So let's think about it. I want the length of the number. The length of 12345 is obviously 5 - so that works. I can convert the number into a string, count the length and get 5. Then I can get -3 from it by subtracting it from 2, which is the number of significant places I want.

Terrific. That'll work for positive integers. But the length of "1.2345" is 6, which clearly isn't right.

Here's where I feel myself turning into a real geek. I'm going to use the log10() function. log10(12345) is 4.09. Better still, log10(999) is 2.9996 and log10(1000) is 3. That's terrific. I can round that number down, add one, and there's my number length. It even works for fractions. log10(1.2345) is a little over 0, log10(0.9) is just under 0. log10(0.00343) is about -2.5. So if I round all these DOWN (that's the floor() function), and add one, I get what I need the length to be. I can't use the ceiling() function, because that wouldn't work for 1000 - for which I want a length of 4, not 3. I do actually need to round down and then add one, in case rounding down doesn't do anything.

So now I have my 'length', and if I subtract that from 2, then hopefully I get the number that I can use for the round function.

alter function dbo.fix(@num float) returns float as
begin
declare @res float
select @res = round(@num,2-(1+floor(log10(@num))))
return (@res)
end 

Well this seems to do it. Of course I could change the 2 to a 1, and stop adding one to my floor. I run my test query, and no rows are returned. Great. All done.

Except that it isn't. High-school maths reminds me that you can't take the log of a negative number. So what's going on with my tests? I check the Messages (rather than the results grid), and I see a domain error has occurred. It'd be nice if this would kick a proper error, one that would display red text and stop my query from running at all, but such is life.

Either way, we know it's a problem now, so we can fix it.

I can't take the log of a negative number, so let's check to see if the number if negative, and if it is, we can use the log of the negative number instead. I mean, I still want to round 3.6 with a value of 1, and -34334 with -3. So I'll put a case statement into my function:

alter function dbo.fix(@num float) returns float as
begin
declare @res float
select @res = case
when @num > 0 then round(@num,1-floor(log10(@num)))
else round(@num,1-floor(log10(-@num)))
end
return (@res)
end

Now my test works without an error message. "(0 row(s) affected)" is what I want to see, and there it is. I'm happy.

A minor change is to let it take another function to show many significant digits are required. Simple change:

alter function dbo.fix(@num float, @digits int) returns float as
begin
declare @res float
select @res = case
when @num > 0 then round(@num,@digits-1-floor(log10(@num)))
else round(@num,@digits-1-floor(log10(-@num)))
end
return (@res)
end

And the guy who asked me for this? Well, he's gobsmacked that there really is a use for logarithms in the real world.

Original Source: http://www.sqlservercentral.com/articles/Advanced+Querying/afixfunctionintsql/2487/