How to diagnose IO Problems

Recently our production database began experiencing very poor I/O
performance. How do I begin to narrow down the queries or tables that are
affecting this?It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:
> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?

How to diagnose IO Problems

Recently our production database began experiencing very poor I/O
performance. How do I begin to narrow down the queries or tables that are
affecting this?It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:

> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?

How to diagnose IO Problems

Recently our production database began experiencing very poor I/O
performance. How do I begin to narrow down the queries or tables that are
affecting this?
It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:

> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?

How To Deploy SSRS To Production Server ?

Hi All

I am having a problem with SSRS reports which I hope you can help with.

We have a web application which contains a couple dozen reports built using SSRS. In order to do a new release I first deploy the web application and the SSRS reports to our pre-production server for testing.

Whilst testing is going on, developers are making further changes to some of the reports and also the the database structure.

My problem is that when I want to move the new release from the pre-production server to the production server, I can copy the application but not the SSRS reports. So my only choice appears to be to deploy the SSRS reports from a devlopment machien directly to the production server.

This is clearly going to cause me problems as those reports may have been further developed and the database structure may have changed sine deploying the application to the pre-production server so I am effectly releasing untested reports directly to the production srever.

So my quesiton is:

How can I copy my SSRS reports from the pre-production server to the production server? Is there another way of dong this?

Regards

Smeat

yes.....

can we just get a link to this tool pinned?

www.sqldbatips.com

has a tool called Reporting Services Scripter 2.0.0.8

1.you point it at RS, set the options, it creates the script + files
2.global find replace in the files i.e. change servernames
3.run script

I suggest you try this in some kind of development environment first to understand all the different options this tool has|||

Thanks for the info.

One of our developers is checking this out.

Smeat

|||

One way of accompishing your goal will be using a version control software like Visual Source Safe. Here's the scenario.

1. Developer develops version 1 of report and check in Visual Source Safe.

2. Designated person (configuration administrator) pushes version 1 of report to pre-production environment.

3. Report is tested by QA group & users in the pre-production environment.

4. QA group and/or users discover errors and/or updates to be done by developers on version 1 of the report.

5. Developer implements updates and fix errors found on version 1 of the report & check in new version into Visual Source Safe (version 2).

6. Repeat step 2 but this time will be version 2 of the report.

7. Repeat step 3. Move to next step (step 8) if QA group & users are OK with updates; otherwise, repeat step 5 - 7

8. Designated person (configuration administrator) pushes final version of report to production.

I hope this helps.

How to Deploy Reports to a Production Environment

Our situation is that we are developing a set of "master" reports locally
using Visual Studio .NET 2003 (and, of course, the Reporting Services
designer). Once the master reports are finished, we will need to install
these reports to our customers' computers (their computers are not a part of
our network, we don't have access to them to deploy from our computers and
the Visual Studio .NET 2003 solution/project). And then we will need to
deploy to the ReportServer on their server.
Additionally, the customers' server will have VB .NET installed (the basic
edition to allow RDL development).
So, should we simply copy the solution, project, RDS, and RDL files to the
customers' server, then open the solution in VB .NET, and deploy? Or is
there a better way?
Also, since we are developing the master reports in a solution/project in
Visual Studio .NET on our local development servers, is there any
compatibility issues in trying to open that same solution in VB .NET on the
customers' server?
Thanks.I would think the easiest way would be to build a quick app to do this using
the CreateReport SOAP Api. You could then make this app as simple or
complex as you need it and not need to worry about your customers having any
VS components. We shipped a sample app (RSExplorer I believe) that does
allow you to deploy but it would have to be modified if you wanted to easily
upload a directory of rdls.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Belak" <nospam@.nospam.com> wrote in message
news:OrhITzzmEHA.3264@.TK2MSFTNGP12.phx.gbl...
> Our situation is that we are developing a set of "master" reports locally
> using Visual Studio .NET 2003 (and, of course, the Reporting Services
> designer). Once the master reports are finished, we will need to install
> these reports to our customers' computers (their computers are not a part
of
> our network, we don't have access to them to deploy from our computers and
> the Visual Studio .NET 2003 solution/project). And then we will need to
> deploy to the ReportServer on their server.
> Additionally, the customers' server will have VB .NET installed (the basic
> edition to allow RDL development).
> So, should we simply copy the solution, project, RDS, and RDL files to the
> customers' server, then open the solution in VB .NET, and deploy? Or is
> there a better way?
> Also, since we are developing the master reports in a solution/project in
> Visual Studio .NET on our local development servers, is there any
> compatibility issues in trying to open that same solution in VB .NET on
the
> customers' server?
> Thanks.
>