April 17, 2020 at 3:45 pm
Hi
Having an issue creating a SP to create indexes for an overnight job on a replicated DB that does not supply non-clustered Indexes
or any other ideas would be appreciated....
The Create index command work fine by themselves I just wanted to add them all in an SP
Thanks
tried
USE [DBname];
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Non-Clustered_Indexes...]
AS
CREATE NONCLUSTERED INDEX.......
CREATE NONCLUSTERED INDEX.......
April 17, 2020 at 4:01 pm
and what is your issue?
what you said does not give us any detail other than what you intend on doing.
April 17, 2020 at 5:10 pm
I agree with frederico_fonseca, this should work. I see no reason why that stored procedure wouldn't work unless you need it to create all of the indexes dynamically in which case that is going to be incredibly difficult.
Also, depending on how many indexes and how many rows you may end up creating a lot of blocking.
I am a little confused about the process though - shouldn't those index creation tasks be a one-time thing or are you dropping all of the indexes nightly?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 17, 2020 at 5:27 pm
Hi
Yes the overnight refresh drop nonclustered idexes, thats why I would run the job after the refresh
I have multiple
CREATE NONCLUSTERED INDEX [ix....
GO
CREATE NONCLUSTERED INDEX [ix....
GO
when I add the header
USE [dbname];
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spname]
AS
it just run the query(creates the indexes) and doesn't create the stored procedure
April 17, 2020 at 5:39 pm
if I understand what you are trying to say it seems that you don't know how to create a stored procedure
so instead of putting a few snippets from your code looks like why don't you add the full create proc as a text file here so we can see what you are doing wrong.
April 17, 2020 at 5:47 pm
USE [Database];
GO
/****** Object: StoredProcedure [dbo].[Non-Clustered_Indexes] Script Date: 4/16/2020 4:34:21 PM ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[Non-Clustered_IndexesB]
AS
BEGIN
CREATE NONCLUSTERED INDEX [ix_Service Note 2010 Selections_Parent] ON [database].[dbo].[CBFS Service Note 2010 Selections]([Parent] ASC) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
GO;
CREATE NONCLUSTERED INDEX [ix_Progress Note Selections_Parent] ON [database].[dbo].[CBHI Progress Note Selections]([Parent] ASC) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
GO;
CREATE NONCLUSTERED INDEX [IX_Appointment_BillingInformation_Client Payer Coverage] ON [database].[dbo].[eHana_Appointment_BillingInformation]([Client Payer Coverage] ASC) INCLUDE([ObjectID]) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
GO;
CREATE NONCLUSTERED INDEX [IX_Appointment_BillingInformation_ObjectID] ON [database].[dbo].[eHana_Appointment_BillingInformation]([ObjectID] ASC) INCLUDE([Procedure Code]) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
GO;
CREATE NONCLUSTERED INDEX [IX_Appointment_BillingInformation_ProviderID] ON [database].[dbo].[eHana_Appointment_BillingInformation]([Billing Provider] ASC) INCLUDE([ObjectID]) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
GO;
END;
April 17, 2020 at 5:51 pm
yup.. you do need to go and learn the basics of T-SQL.
remove all "GO" statements that are after the create procedure.
But as Brian mentioned why this? once created the indexes remain created unless you are dropping them.
April 17, 2020 at 5:54 pm
What does this 'overnight refresh' process actually do?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 17, 2020 at 6:26 pm
To be honest the vendor won't tell use. It is transaction replication for reporting
All I know is they won't turn on nonclustered inxedes in replication
thus the recreation each night
April 17, 2020 at 6:44 pm
Is the vendor tool dropping the indexes OR are you dropping them?
If it is you, why not disable them and then rebuild rather than dropping and recreating?
Do you need the indexes? If the vendor tool doesn't support nonclustered indexes, maybe they have a reason for that (apart from slower inserts/updates/deletes).
My advice would be to reach out to the vendor to see what other clients do rather than trying to "fix" a vendor process. One vendor we purchased software from decided that turning OFF page locks on an index was a good idea and it broke the reorganize I was doing on their indexes. The response from the vendor was that turning off page locks was for performance and that I should stop doing any maintenance on their system apart from backups. Everything else was "handled" by them and to stop doing index maintenance.
TheTL;DR version of the above is - Check with the vendor what other clients do. You may be able to disable the indexes and rebuild them instead of dropping and recreating which is likely to be a much more simple process AND can be dynamic so if the vendor tool pushes across another table due to an update, you can automagically handle it. The drop and recreate method will require more maintenance on your part.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 18, 2020 at 2:07 am
To be honest the vendor won't tell use. It is transaction replication for reporting
All I know is they won't turn on nonclustered inxedes in replication
thus the recreation each night
I'd be looking for a new vendor. I damned sure wouldn't let them work on my databases if they're going to cop such an attitude while demonstrating such ignorance. Like the robot used to say, "DANGER! DANGER!"
--Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply