Slow Running, No Morning Duty Roster, KISS Migration
-
Slow Running
: We have a load running that runs fine on one SQL server machine and slow on the other. What could I check to try to identify the problem? We think we dropped all of our index's before the job stared ... how do we know if an index is being used?No Morning Duty Roster
: I've stepped in to manage a SQL Server DBA area and it seems that the DBAs do mostly programming. They have no procedures/processes in place to monitor and/or check the database at the beginning of a business day, during, and at the end. It all seems very reactive ... as users complain about results the dba's run checktables. What procedures/processes should I put in place to proactively monitor and manage my DBAs and Users?KISS Migration
: It seems to be quite a clunky process to migrate solution changes from our development to our production servers...copying and pasting stored procedures, exporting/importing dts then generating each job parameter stream on the new machine, and implementing ddl. Am I missing something? Is there an easier way?Thanks! ... A whole bunch! for any reply
-
Slow Running : Check the estimated execution plan on each server for differences. (from Query analyzer)
Duty roster: Maintenance jobs should be run on all databases, that is Backups, Reindex tabales, update statistics, checkdb etc. All these jobs should message the DBA (via email or whatever) should they fail. So I would ensure these jobs all exist. I would also ensure that notification of job failure occurs (there are many ways to do this) The point is the DBA needsto know immediately if one of these jobs fails. There may be other jobs required depending on your installation. I aslo run a job that checks space used and available so I know if I'm about to run out of storage and how quickly the databases are growing. I have another job that cycles the error log if it gets to big.
KISS Migration: I like to use scripts for migration. This does not work with DTS packages but see http://vyaskn.tripod.com/sql_server_dts_best_practices.htm for ideas on how to deploy DTS. DTS is more complex since references to specific servers may exist and these may need to be changed in production. If you want there are a number of tools that can help with this including compare tools that can generate scripts to synchronize databases
Francis
-
Slow Running:
Can you give me the Query analyzer code? I have not done this before.Duty Roster:
Ccould you expand the etc? Does updating statistics impact performance? Can I get the code for your job that checks space used and available ... does it estimated projected utilization based on projected consumption? Can I get the code to cycle through the error log?KISS Migration:
What tools help with the migration / synchronization?Thanks
-
Slow Running. Eiterh use SET SHOWPLAN_TEXT ON
GO
your query goes here
GO
SET SHOWPLAN_TEXT OFF
GO
or else use CTRL-L (Query\Display Estimated Execution Plan) from the QA menu. The difference is a text plan vs a more pictoral plan. There are a number of articles on interpreting the output on this site.
Duty Roster: The etc refers to other jobs that may be required/desired by your site. I am monitoring 5 production servers and I have some maintenance jobs that are required on some servers and not on others. For example on 1- and only 1 server- I run a job that deletes any users not in a server roles and without specific database access. This is an issue with the application on that server and is not required on other machines.
Updating statistics can impart performance. Run this in an offpeak time. Make sure your databases ar NOT set to AutoClose or to AutoShrink
Monitoring space: Its fairly involved. I have to created linked servers to all prodction machings. I actually have seeral procs which check different stuff and store their results ain a table which other procs read and analyze. If you do a search on this site for 'monitor space' you will get more info/code.
Error log cycle (put this in a job)
CREATE TABLE #logs
( Lognum INT
, Logdate DATETIME
, Size FLOAT
 
INSERT INTO #logs EXEC master.dbo.xp_enumerrorlogs
IF ( SELECT size FROM #logs WHERE LogNum = 0) > 1000000
BEGIN
EXEC sp_cycle_errorlog
END
DROP TABLE #logs
Migration: Embarcadero makes a product called Change Manager and Artisan both of which could help. ApexSQL makes ApexSQL Diff and Red-Gate has a toolkit for syncronization and comparing. There are others but this is off the top of my head. Anyone else use 3rd party tools for deployment?
Francis
-
In the database, select * from sysindexes will tell you if they are there. This site has lots of info in stuff to watch for.
-
I think also you have to update statistics
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply