sql indexing, optimization

When considering optimization, indexing is one of the options. The two types of indexing.
1. clustering index and 2. non- clustering index.

Clustering index is referred to like book shelf where the files in the shelf are refered to as pages, records in the files are referred to as the rows in the table, the shelf drawers on its own by alphabetical order is referred to as intermediate level.

Non clustered index is referred to as the index at the back of the book, where in a book when looked for a particular topic you can see references of certain pages based on that topic, in similar way non clustered index references the pages of similar key.

The next step will be to how effectivily shall we use the indexing. The main two functions of a indexing is to provide uniqueness and to return results much faster.

The next step is choosing between cluster and non-cluster index. We can have only one cluster index per table, where we can have 249 non - cluster index on a table. So in most cases we have the primary key set as the cluster index and work on the selectivity to determine the need of the number of non-cluster indexes.

Now how to determine and set up indexes.

For example if you are looking for a word "customer" in a book then a index will help you to find all the pages where the customer is available.

For example if you are looking to search all the words in a book then it is better to read the ent book which is table scan in our sql query, so in this case a index will be of no beneficial only burden.

There are a few ways to determine the need for a index, sql profiler is one of our best tools to start with, to check where there is more time spent.

Then based on the time, when the tables are identified this query can be used to determine the selectivity ratio.

selectivity

select count(distinct salesordernoitem) as '# unique',
count(*) as '# rows',
str(count(distinct salesordernoitem) / cast (count(*)as real),4,2) as
'selectivity' from transactionpricing

if the results are as below

#unique #rows selectivity
75000 75000 1.00

in this case an index on the sales order number in the transaction pricing table will be appropriate.

SQL SSRS Linked Reports

When a report is developed and in production for the sales for the emea region, if one sales person needs to view reports only for Ireland this can be achieved using linked reports.

Using the Report Manager --- Click the base report where the linked report to be created.

Then click the properites tab
Click on the Linked report.
You can provided the name of the linked report.
The location where the linked report is saved can be changed if required.
On the side you can see the parameters, datasources, execution, security and history.

• Remember for the linked report there is no separate report definition file (.rdl file).
• Also the parameters can have a different list of selected parameters from the base report but not a different SET of parameters list from the base report.
• As the rdl file is the same the changes performed on the base report implies to the linked report, so this could be a advantage in some scenarios and disadvantage in some cases, it is upto the user to put in use.

SQL SSRS ad hoc report generation

In sql ssrs we have an option where the users can generate there own reports and store them in there own location. To develop ad hoc reports by end users the following need to be provided by the administrator.

Go to the report manager page.
Click on the datasource.
General a report model.

Also the user can generate the ad hoc reports provided he has the option given to generate reports and save them in the Myreports folder.

This will allow the users to generate ad hoc reports from their own web browsers.

SQL SSAS Dimension relationships

There are four main sql ssas dimension relationships. Regular, Referenced, Many-to-Many and Fact.

Regular - Is used when relationship of a dimension is linked directly to a measure group through the attribute.

Reference - Is used when relationship of a dimension is linked directly to a measure group through a intermediate dimension.

Many-to-Many - Is used when relationship of a dimension is linked through a measure group through an intermediate measure group.

Fact - Is used when relationship of a dimension is linked through a fact table to define the measure group.

If anyone can elaborate further on this, you are welcome.

SQL SSAS Backup

The sql ssas cube is very important to be backed up. This can be done using the sql server manaement studio.

The following steps to BACKUP the ssas cubes.

Connect to the analysis servvices. Right click the cube to be backed up.

Click on Backup. Uncheck File compression --- this is used compress the backup file at the expense of performance.Uncheck the encrytion --- this can be checked if the backup file need to be pasword protected in that case a password has to be provided.
check the file overwrite option based on your requirement

Click Browse in the selected path enter the folder where the backup file to written.
Now click on the script and script action to new query window
This will write a query to query window. Copy this and open a new job in the sql agent.

Scheduling the backup using sql agent

Click New job
provide a name for the job , select step, select type as SQL SERVICES ANALYSIS COMMAND, Enter the server , past the script below

SQL SSRS Report performance

SQL SSRS Reports performance can be enhanced using the cache and snapshots.

To execute a report, when the request is sent though HTTP, the rdl is called based on the paramters passed and the dataset queries based on the query, if the query has to return a large number of rows, there is a possibility of time out error or locking.

To avoid this cache can be used, i.e., if the same paramters are used by the user report can be generated from the Report server tempdb , and report server renders from the stored intermediate format. Please not the cache will not work if the report credentials are saved in the report server.

Snapshots : Cache reports do not preserve the previous versions of the report generation, i..e, history in that case snap shots come in place , you can store prevous versions in the report history and this can stored by a schedule, by specifiing the number of reports can be stored.

This can be setup in the http://localhots/reports site settings --- select default settings for the history ---

please note keeping unlimited number of snapshots in history make create disk space issue.

Enjoy developing ssrs reports.

SQL SSRS item level and system level roles

In sql 2005 we can specifiy item and system level role definitions. The item level and system level role definitions can be set by going http://localhost/reports

site settings --- Configure item level role definitions for item level
and Configure system level role definitions for system level

The item level role definitions are :

• Consume reports
• Create linked reports
• Manage all subscriptions
• Manage individual subscriptions
• Manage folders
• Mange reports
• Manage models
• Manage resources
• Set security for individual items
• View data sources
• View models
• View folders
• View reports
• View resources

But by default the sql 2005 provides only Browser, Content Manger, Report Builder, Publisher and My reports

The system level role definitions includes

• Execute report definitions
• Generate Events
• Manage jobs
• Manage report server properties
• Manage roles
• Manage shared shedules
• Manage report server security
• View report server
• View shard schedules