Installation and Technical Issues

[Jima]

We have been looking into ways to improve server performance and offset some of the reporting we do on our MAS500 database. One idea we have is to set up transactional replication and using the replication database for reporting, leaving the 'production' database for transactions. I realize we will not be able to force all reporting to the replication DB, but we could move Explores and some of our external apps, which I think would help reduce some of the performane requirements.

Does anyone have any tips or potential issues for this method? If anyone has done anything similar and would share their experiences, it would be very interesting to hear.

One potential issue mentioned to me was handling an upgrade with replication in place. However, I believe this could be handled by removing the replication set up and then re-establishing a new replication afterwards. Since we do not upgrade versions often, this would not be a major issue (although of course it would have an impact on the whole upgrade process).

Thanks!

[JohnHanrahan]

You mention performance requirements and the word I think.  Does this mean you have not identified what processes are slowing your system down?  If you want to move your explorers to a different server how would that work?  Users would have to login and out if they wanted to use customer maintenance then do an inventory view. 

I have used this script to begin looking for highly utilized queries to see if I can tweak them for speed:

-- Wait Top 20
--select  * FROM sys.dm_exec_requests
USE [mas500_app]
GO

select top 20 st.objectid, so.name, st.dbid, total_worker_time/execution_count AS AverageCPUTime,
 CASE statement_end_offset WHEN -1 THEN st.text ELSE SUBSTRING(st.text,statement_start_offset/2,statement_end_offset/2) END AS StatementText
 from  sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
 LEFT OUTER JOIN sys.objects so
  ON st.objectid = so.object_ID
ORDER BY AverageCPUTime DESC

One of the baddies I had found running this was that we were using functions in our views.  These drastically slow down processing in views.  By removing and rewriting one view the pull up time for 1000 records (or so) went from 38 seconds to 1.

I would also review some of Joe Noll's posts, he has some very good stuff on what to look for SQL setup wise.

J

[jnoll]

I like what John has done with his filtered views. You may also consider Filegroup or Partitioning to increase the performance. These options would both need to have adequate hardware and the proper software configuration to make them viable options but they do work very well properly configured.

We have configured a Replicated Server for 2 clients but they have another tool that they are using for their reporting and data mining so it was a viable option. This can be configured several ways. You want to get as much of the overhead of this process off of your MAS server.

I will share my notes with you if you like. Just drop me an email and I will send you the document.