Comments
Sort by recent activity
See https://redgate.uservoice.com/forums/14 ... al-objects / comments
See https://redgate.uservoice.com/forums/14 ... al-objects
The changes are not flagged by SQL Compare because they are not there yet.
I'm trying to use the SQL compare to script out a selected range of objects in one script and then implement the changes on that script.
Think big refactors eg:
*changing a column name in a range of tables and/or views that are not always "interdependent"
*replacing a UDF with inline code
*changing filters and business rules in a range of views
*applying style-formatting to a range of objects
so i'd have a script like :
ALTER VIEW vw1
ALTER VIEW vw2
... / comments
The changes are not flagged by SQL Compare because they are not there yet.
I'm trying to use the SQL compare to script out a selected range of objects in one script and then implement the changes o...
Eddie
Thanks for the feedback.
We use the dedicated model. I was mostly just wondering why tempdb is used and why these suggested indices would not be pre-included.
I often use the missing_indices DMV (performance dashboard) to spot performance problems and I stumbled upon these suggested indices many of times so I thought I'd ask around on the forum to satisfy my curiosity
Btw I was wrong to blame the DTA (in this instance) , it was the missing indices dmv/report
Lx / comments
Eddie
Thanks for the feedback.
We use the dedicated model. I was mostly just wondering why tempdb is used and why these suggested indices would not be pre-included.
I often use the missing_indices ...
ALTER PROC MS_PerfDashboard.usp_Main_GetRequestWaits
as
begin
SELECT
r.session_id,
MS_PerfDashboard.fn_WaitTypeCategory(r.wait_type) AS wait_category,
r.wait_type,
r.wait_time
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s ON r.session_id = s.session_id
WHERE r.wait_type IS NOT NULL
AND s.is_user_process = 0x1 AND r.wait_type<>'WAITFOR' / comments
ALTER PROC MS_PerfDashboard.usp_Main_GetRequestWaits
as
begin
SELECT
r.session_id,
MS_PerfDashboard.fn_WaitTypeCategory(r.wait_type) AS wait_category,
r.wait_type,
r.wait_time
FROM sys.dm_exec_requ...
LTER PROC MS_PerfDashboard.usp_RequestWaits
as
begin
select r.session_id,
r.request_id,
master.dbo.fn_varbintohexstr(r.sql_handle) as sql_handle,
master.dbo.fn_varbintohexstr(r.plan_handle) as plan_handle,
case when LEN(qt.query_text) < 2048 then qt.query_text else LEFT(qt.query_text, 2048) + N'...' end as query_text,
r.statement_start_offset,
r.statement_end_offset,
r.wait_time,
r.wait_type,
r.wait_resource,
msdb.MS_PerfDashboard.fn_WaitTypeCategory(wait_type) as wait_category
from sys.dm_exec_requests r
join sys.dm_exec_sessions s on r.session_id = s.session_id
outer apply msdb.MS_PerfDashboard.fn_QueryTextFromHandle(r.sql_handle, r.statement_start_offset, r.statement_end_offset) as qt
where r.wait_type is not null and s.is_user_process = 0x1 AND r.wait_type<>'WAITFOR' / comments
LTER PROC MS_PerfDashboard.usp_RequestWaits
as
begin
select r.session_id,
r.request_id,
master.dbo.fn_varbintohexstr(r.sql_handle) as sql_handle,
master.dbo.fn_varbintohexstr(r.plan_handle) as plan...
Chris,
Do you get what i mean?
Is use the ignore options to only detect some differences (my software is also uesd by another company so and some options are different like SQLUsers indexes etc... )
But it seems that when a table rebuild occurs, the table is not fully rebuilt when some ignore options are on / comments
Chris,
Do you get what i mean?
Is use the ignore options to only detect some differences (my software is also uesd by another company so and some options are different like SQLUsers indexes etc... ...
One way to do this is to add views to the msdb databases on both nodes/servers that act as a non-system-object "proxy" You can then use SQL DATA Compare with the option "include views" and then set comparison keys in SQL DATA COMPARE tables & views to use the names instead of uuids and then exclude identifier/modified/version columns from comparison e.g
USE msdb;
GO
CREATE OR ALTER VIEW sysjobs_proxy
AS
SELECT *
FROM msdb.dbo.sysjobs AS j;
GO
CREATE OR ALTER VIEW sysjobsteps_proxy
AS
SELECT st.*,
j.job_id AS j_job_id,
j.name AS job_name
FROM dbo.sysjobsteps AS st
INNER JOIN dbo.sysjobs AS j
ON j.job_id = st.job_id;
GO
CREATE OR ALTER VIEW sysjobschedules_proxy
AS
SELECT jsc.*,
j.job_id AS j_job_id,
j.name AS job_name,
sc.name AS shedule_name,
sc.schedule_id AS sc_schedule_id
FROM msdb.dbo.sysjobschedules AS jsc
INNER JOIN dbo.sysjobs AS j
ON j.job_id = jsc.job_id
INNER JOIN dbo.sysschedules AS sc
ON sc.schedule_id = jsc.schedule_id;
GO
CREATE OR ALTER VIEW sysschedules_proxy
AS
SELECT *
FROM msdb.dbo.sysschedules AS s; However - this does not yield any usable scripts to switch job-step order etc.. It's easier to script out the job with DROP and CREATE and alter top portion
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name='JOBNAME'
BEGIN
DECLARE @jobId BINARY(16)
SELECT @jobId=job_id FROM msdb.dbo.sysjobs WHERE name='JOBNAME'
EXEC msdb.dbo.sp_delete_job @job_id=@jobId, @delete_unused_schedule=1
END
/ comments
One way to do this is to add views to the msdb databases on both nodes/servers that act as a non-system-object "proxy"You can then use SQL DATA Compare with the option "include views"and then set c...