Hello Friends,
I would like to thank POM who gave both myself and PREM an opportunity to interact with you and allowed us to present CDC, Change Tracking and Intrduction to MDX on 27th Aug 2009 at SQL UG Meeting.
You can download presentation and demo scripts from the DOWNLOAD FILES link on the top right side of the page. If you have any problem with download, write to me with your email address & I will send it directly to you.
Appreciate if you let me know your comments or feedback.
Thanks & see you all in Sept 2009 SQL UG meeting.
Happy Coding.
Monday, August 31, 2009
Wednesday, August 19, 2009
SQL User Group Meeting on 27th Aug
Hi,
Welcome to this month's SQL Server user group meeting on 27th Aug at Microsoft. The agenda for this meeting is to:
Understand the architecture and implementation of Change Data Capture and Change Tracking
What is MDX, Multi-Dimensional, Testing MDX with the Query Tool in SQL Server Management Studio and Basic Elements of an MDX Query
The presentaiton would give an idea to how to work with Cube and use MDX(es) to retrieve data. Thinking Multidimensional, Testing MDX with Query Tool in SQL Server Management Studio and Basic Elements of an MDX Query would be some of the topics covered in this session.
Speaker Profile:
Prem Shanker, Sr. Software Engineer (Business Intelligence) ,Credit Suisse
Prem Shanker Pandey is a senior software engineer working with Credit-Suisse. He has been working on ETL/ Data warehouse technologies for last 3 years and has deep understanding of implementation of SSAS, SSIS & SSRS 2005/2008.
His day to day activities include work/enhance/modify SSAS cubes, performance tuning, Load data from various sources for SSAS cube using SSIS (integration Services), Write/optimizes MDX queries (SSRS reporting).
Prior to his current position, he was working with Perot System, Singapore and used to provide Consulting, Application Development and maintenance services to Jurong Town Corporation, Singapore Police Force etc.
Sudhir Chawla, Sr. Consultant (SQL SERVER) ,Credit Suisse
Sudhir Chawla is a Consultant with Zensar and working for Credit Suisse. His day to day activities include SQL Server 2005 query tuning, performance enhancement, develop SSRS reports, Monitoring database and .Net technology.
He has around 10 years of experience of various domains, like HR, Finance, Cruise, CRM etc. Prior to this he has worked with NEC and Siemens, Singapore.
Download demo script and Presentation
After the meeting presentation will be available for download along with demo scripts. Watch out for the download link.
SQL SERVER 2008 R2 is released.
Important to note that Microsoft has released SQL Server 2008 R2 Aug CTP on 12th August to public, so if you are interested then download it now from http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx.
To play around with GEMINI, click here https://connect.microsoft.com/SQLServer
If you have any specific query regarding CDC, please write to me and I will try to address that in the UG meeting.
Hope to meet you all during the meeting on 27th August, Thursday at 7PM.
Regards,
Sudhir
Welcome to this month's SQL Server user group meeting on 27th Aug at Microsoft. The agenda for this meeting is to:
The presentation will focus on 2 different Auditing functions provided by SQL Server 2008, compare both the features and how easy it is to implement these in our application.
The presentaiton would give an idea to how to work with Cube and use MDX(es) to retrieve data. Thinking Multidimensional, Testing MDX with Query Tool in SQL Server Management Studio and Basic Elements of an MDX Query would be some of the topics covered in this session.
Speaker Profile:
Prem Shanker, Sr. Software Engineer (Business Intelligence) ,Credit Suisse Prem Shanker Pandey is a senior software engineer working with Credit-Suisse. He has been working on ETL/ Data warehouse technologies for last 3 years and has deep understanding of implementation of SSAS, SSIS & SSRS 2005/2008.
His day to day activities include work/enhance/modify SSAS cubes, performance tuning, Load data from various sources for SSAS cube using SSIS (integration Services), Write/optimizes MDX queries (SSRS reporting).
Prior to his current position, he was working with Perot System, Singapore and used to provide Consulting, Application Development and maintenance services to Jurong Town Corporation, Singapore Police Force etc.
Sudhir Chawla, Sr. Consultant (SQL SERVER) ,Credit Suisse
Sudhir Chawla is a Consultant with Zensar and working for Credit Suisse. His day to day activities include SQL Server 2005 query tuning, performance enhancement, develop SSRS reports, Monitoring database and .Net technology.
He has around 10 years of experience of various domains, like HR, Finance, Cruise, CRM etc. Prior to this he has worked with NEC and Siemens, Singapore.
Download demo script and Presentation
After the meeting presentation will be available for download along with demo scripts. Watch out for the download link.
SQL SERVER 2008 R2 is released.
Important to note that Microsoft has released SQL Server 2008 R2 Aug CTP on 12th August to public, so if you are interested then download it now from http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx.
To play around with GEMINI, click here https://connect.microsoft.com/SQLServer
If you have any specific query regarding CDC, please write to me and I will try to address that in the UG meeting.
Hope to meet you all during the meeting on 27th August, Thursday at 7PM.
Regards,
Sudhir
Tuesday, August 4, 2009
Performance Tips - III
Guys,
Let's discuss a few more performance tips. Application performance is driven by a number of different factors. One of those items that typically affect performance is how long it takes SQL Server to process T-SQL statements. Sometimes the database design and the complicated query requirements hinder the speed at which T-SQL statements can run. Other times the way the T-SQL statements are coded cause SQL Server to perform extra work to resolve a query. As we know one query can be written in many ways, so by rewriting the statements differently, you can help the SQL Server engine optimize your queries and improve performance.
Identify Column Names in Your INSERT statements
We should explicitly identify the column names with an INSERT statement. Don’t code your INSERT statements like this:
INSERT INTO MyTable VALUES ('A','B','C');
When we use this coding style, SQL Server requires that only three columns be defined in MyTable, and the value “A” would go in the first column, “B” in the second column and “C” in the last column. If someone adds a new column to MyTable your application code will break with the following error:
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
Therefore, instead of using the above coding style for your INSERT statements you should code them like this:
INSERT INTO MyTable(FirstCode, SecondCode, ThirdCode) VALUES ('A','B','C');
By doing this when someone adds a new column named “FourthCode” to MyTable the above INSERT statement will continue to work, provided the “FourthCode” column was created with a DEFAULT value or allows NULLS.
Speed Up Your Searches by Prefixing Wild Card References
Appropriate use of wild card can improve the performance of your queries. Say we want to search the AdventureWorks.Person.Contact table for all the LastNames ending in “sen”. For a moment, let’s assume that we have also built an index on the LastName column. If we code your search like so:
SELECT Distinct LastName
FROM Person.Contact
WHERE LastName LIKE '%sen'
The code uses the wild card percent (%) character to match zero to many characters followed by the string “sen” in the LastName field. This will cause SQL Server to perform an index scan operation looking for all the names that end in “sen” to resolve this query. This makes sense because until the entire table is read (scanned) SQL Server can’t guarantee that it has found all the records where the LastName ends in “sen”.
In addition, if we were searching for LastNames that where exactly six characters long and ended in “sen” we could code our wild card search like this:
SELECT Distinct LastName
FROM Person.Contact
WHERE LastName LIKE '___sen'
Here we have used the underscore (_) wild card character. This wild card character is used to match a single character. This coding example is similar to the prior example, and uses an index scan operation to resolve it. Once again, the SQL Engine knows it has to scan the complete index before it knows it has found all the six character names that end in “sen” in the Person.Contact table.
SQL Server can return your results faster if it doesn’t have to read the entire index using a scan operation. SQL Server is smart enough to know when we place a prefix of some kind in front of your percent (%) and/or underbar (_) wild card characters that it can use an index seek operation to resolve the wild card search criteria. Here is an example of a statement that will return all the LastNames that start with “A” and end in “sen”:
SELECT Distinct LastName
FROM Person.Contact
WHERE LastName LIKE 'A%sen'
By putting the “A” character in front of the percent (%) sign in the search criteria SQL Server is now able to tell that it can use an index seek operation to resolve this query. This make sense because once SQL Server has gotten to the end of the “A” for last name , it knows there are no more last names that start “A” so it can stop processing.
Not all wildcard characters need to be prefixed in order to make SQL Server use an index seek operation to resolve a query. Here is an example of where we can use a range wildcard expression and still have SQL Server resolve this query using an index seek operation:
SELECT Distinct LastName
FROM Person.Contact
WHERE LastName LIKE '[A-M]%sen'
In this T-SQL statement, we are looking for all the LastNames that start with anything between an “A” through “M” and end with “sen”. Other wild card syntax that identifies a specific set of characters can also invoke an index scan operation to resolve a wild card search criteria.
Only Use DISTINCT If You Need It
Placing the DISTINCT clause on our SELECT statement takes the results of our query and removes the duplicates. It takes SQL Server an extra SORT operation to sort the data so it can identify and remove the duplicates. Therefore, if we already know that results will not contain duplicates than don’t put the DISTINCT clause on our T-SQL statement. By putting the DISTINCT clause on our query, we are telling SQL Server to perform the sort and un-duplication process. This is extra work for SQL Server and provides no value when our result set only contains unique sets of records in the first place.
Faster Code by Using Best Practices
There are many reasons to follow best practices. Some best practices help your application code not break when schema changes are made, while other best practices improve the performance of your queries, by minimizing CPU, I/O and network bandwidth. This set of best practices covered some simple things you can do to minimize the resources used by SQL Server when processing your SQL Server statements.
Will come back with more tips.
Happy SQL coding.
Let's discuss a few more performance tips. Application performance is driven by a number of different factors. One of those items that typically affect performance is how long it takes SQL Server to process T-SQL statements. Sometimes the database design and the complicated query requirements hinder the speed at which T-SQL statements can run. Other times the way the T-SQL statements are coded cause SQL Server to perform extra work to resolve a query. As we know one query can be written in many ways, so by rewriting the statements differently, you can help the SQL Server engine optimize your queries and improve performance.
Identify Column Names in Your INSERT statements
We should explicitly identify the column names with an INSERT statement. Don’t code your INSERT statements like this:
INSERT INTO MyTable VALUES ('A','B','C');
When we use this coding style, SQL Server requires that only three columns be defined in MyTable, and the value “A” would go in the first column, “B” in the second column and “C” in the last column. If someone adds a new column to MyTable your application code will break with the following error:
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
Therefore, instead of using the above coding style for your INSERT statements you should code them like this:
INSERT INTO MyTable(FirstCode, SecondCode, ThirdCode) VALUES ('A','B','C');
By doing this when someone adds a new column named “FourthCode” to MyTable the above INSERT statement will continue to work, provided the “FourthCode” column was created with a DEFAULT value or allows NULLS.
Speed Up Your Searches by Prefixing Wild Card References
Appropriate use of wild card can improve the performance of your queries. Say we want to search the AdventureWorks.Person.Contact table for all the LastNames ending in “sen”. For a moment, let’s assume that we have also built an index on the LastName column. If we code your search like so:
SELECT Distinct LastName
FROM Person.Contact
WHERE LastName LIKE '%sen'
The code uses the wild card percent (%) character to match zero to many characters followed by the string “sen” in the LastName field. This will cause SQL Server to perform an index scan operation looking for all the names that end in “sen” to resolve this query. This makes sense because until the entire table is read (scanned) SQL Server can’t guarantee that it has found all the records where the LastName ends in “sen”.
In addition, if we were searching for LastNames that where exactly six characters long and ended in “sen” we could code our wild card search like this:
SELECT Distinct LastName
FROM Person.Contact
WHERE LastName LIKE '___sen'
Here we have used the underscore (_) wild card character. This wild card character is used to match a single character. This coding example is similar to the prior example, and uses an index scan operation to resolve it. Once again, the SQL Engine knows it has to scan the complete index before it knows it has found all the six character names that end in “sen” in the Person.Contact table.
SQL Server can return your results faster if it doesn’t have to read the entire index using a scan operation. SQL Server is smart enough to know when we place a prefix of some kind in front of your percent (%) and/or underbar (_) wild card characters that it can use an index seek operation to resolve the wild card search criteria. Here is an example of a statement that will return all the LastNames that start with “A” and end in “sen”:
SELECT Distinct LastName
FROM Person.Contact
WHERE LastName LIKE 'A%sen'
By putting the “A” character in front of the percent (%) sign in the search criteria SQL Server is now able to tell that it can use an index seek operation to resolve this query. This make sense because once SQL Server has gotten to the end of the “A” for last name , it knows there are no more last names that start “A” so it can stop processing.
Not all wildcard characters need to be prefixed in order to make SQL Server use an index seek operation to resolve a query. Here is an example of where we can use a range wildcard expression and still have SQL Server resolve this query using an index seek operation:
SELECT Distinct LastName
FROM Person.Contact
WHERE LastName LIKE '[A-M]%sen'
In this T-SQL statement, we are looking for all the LastNames that start with anything between an “A” through “M” and end with “sen”. Other wild card syntax that identifies a specific set of characters can also invoke an index scan operation to resolve a wild card search criteria.
Only Use DISTINCT If You Need It
Placing the DISTINCT clause on our SELECT statement takes the results of our query and removes the duplicates. It takes SQL Server an extra SORT operation to sort the data so it can identify and remove the duplicates. Therefore, if we already know that results will not contain duplicates than don’t put the DISTINCT clause on our T-SQL statement. By putting the DISTINCT clause on our query, we are telling SQL Server to perform the sort and un-duplication process. This is extra work for SQL Server and provides no value when our result set only contains unique sets of records in the first place.
Faster Code by Using Best Practices
There are many reasons to follow best practices. Some best practices help your application code not break when schema changes are made, while other best practices improve the performance of your queries, by minimizing CPU, I/O and network bandwidth. This set of best practices covered some simple things you can do to minimize the resources used by SQL Server when processing your SQL Server statements.
Will come back with more tips.
Happy SQL coding.
Subscribe to:
Posts (Atom)
