What can I do to speed up my instance of SL? Some processes take hours to run when it should only take minutes. SQL Queries and reports take a lot longer to run than they should.
*This post is locked for comments
You can use something like this to Rebuil or Reorg the indexes debending of the fragmentation grade.
USE master
DECLARE @DBName varchar(100), @DBIDS int, @Row int, @Rows int
DECLARE @Databases_Var TABLE (Row int, DBName varchar(100), DBIDS int)
INSERT INTO @Databases_Var
SELECT ROW_NUMBER() OVER(ORDER BY Database_ID ASC) AS Row, name, database_id
FROM sys.databases
WHERE State = 0
--AND name NOT IN ('master', 'tempdb', 'model', 'msdb')
AND name = 'wi381502_AppsMexico'
SET @Rows = @@ROWCOUNT
SET @Row = 1
WHILE (@Row <= @Rows)
BEGIN
SELECT @DBName = DBName, @DBIDS = DBIDS FROM @Databases_Var WHERE Row = @Row
BEGIN
EXEC(' USE ' + @DBName + '
SELECT DB_NAME()
DECLARE @reorg_frag_thresh float SET @reorg_frag_thresh = 10.0
DECLARE @rebuild_frag_thresh float SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor tinyint SET @fill_factor = 80
DECLARE @report_only bit SET @report_only = 1
DECLARE @page_count_thresh smallint SET @page_count_thresh = 500
DECLARE @objectid int
DECLARE @indexid int
DECLARE @partitioncount bigint
DECLARE @schemaname nvarchar(130)
DECLARE @objectname nvarchar(130)
DECLARE @indexname nvarchar(130)
DECLARE @partitionnum bigint
DECLARE @partitions bigint
DECLARE @frag float
DECLARE @page_count int
DECLARE @command nvarchar(4000)
DECLARE @intentions nvarchar(4000)
DECLARE @table_var TABLE(
objectid int,
indexid int,
partitionnum int,
frag float,
page_count int
)
SET NOCOUNT ON
IF EXISTS(SELECT Name FROM sys.databases WHERE name = ''' + @DBName + ''' and recovery_model <> 3)
BEGIN
CHECKPOINT
ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE WITH NO_WAIT
WAITFOR DELAY ''00:00:02''
END
INSERT INTO @table_var
SELECT [object_id] AS objectid,
[index_id] AS indexid,
[partition_number] AS partitionnum,
[avg_fragmentation_in_percent] AS frag,
[page_count] AS page_count
FROM
sys.dm_db_index_physical_stats (' + @DBIDS + ', NULL, NULL , NULL, ''LIMITED'')
WHERE [avg_fragmentation_in_percent] > @reorg_frag_thresh
AND page_count > @page_count_thresh
AND index_id > 0
DECLARE partitions CURSOR FOR
SELECT * FROM @table_var
OPEN partitions
WHILE (1=1) BEGIN
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag, @page_count
IF @@FETCH_STATUS < 0 BREAK
SELECT @objectname = QUOTENAME(o.[name]),
@schemaname = QUOTENAME(s.[name])
FROM sys.objects AS o WITH (NOLOCK)
JOIN sys.schemas as s WITH (NOLOCK) ON s.[schema_id] = o.[schema_id]
WHERE o.[object_id] = @objectid
SELECT @indexname = QUOTENAME([name])
FROM sys.indexes WITH (NOLOCK)
WHERE [object_id] = @objectid AND [index_id] = @indexid
SELECT @partitioncount = count (*)
FROM sys.partitions WITH (NOLOCK)
WHERE [object_id] = @objectid AND [index_id] = @indexid
SET @intentions =
@schemaname + N''.'' +
@objectname + N''.'' +
@indexname + N'':'' + CHAR(13) + CHAR(10)
SET @intentions =
REPLACE(SPACE(LEN(@intentions)), '' '', ''='') + CHAR(13) + CHAR(10) +
@intentions
SET @intentions = @intentions +
N'' FRAGMENTATION: '' + CAST(@frag AS nvarchar) + N''%'' + CHAR(13) + CHAR(10) +
N'' PAGE COUNT: '' + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10)
IF @frag < @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N'' OPERATION: REORGANIZE'' + CHAR(13) + CHAR(10)
SET @command =
N''ALTER INDEX '' + @indexname +
N'' ON '' + @schemaname + N''.'' + @objectname +
N'' REORGANIZE; '' +
N'' UPDATE STATISTICS '' + @schemaname + N''.'' + @objectname +
N'' '' + @indexname + '';''
END
IF @frag >= @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N'' OPERATION: REBUILD'' + CHAR(13) + CHAR(10)
SET @command =
N''ALTER INDEX '' + @indexname +
N'' ON '' + @schemaname + N''.'' + @objectname +
N'' REBUILD''
END
IF @partitioncount > 1 BEGIN
SET @intentions = @intentions +
N'' PARTITION: '' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
SET @command = @command +
N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10))
END
IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
SET @intentions = @intentions +
N'' FILL FACTOR: '' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
SET @command = @command +
N'' WITH (FILLFACTOR = '' + CAST(@fill_factor AS nvarchar) + '')''
END
IF @report_only = 0
BEGIN
SET @intentions = @intentions + N'' EXECUTING: '' + @command
PRINT @intentions
EXEC (@command)
END
ELSE
BEGIN
PRINT @intentions
END
PRINT @command
END
CLOSE partitions
DEALLOCATE partitions
SET NOCOUNT OFF
')
END
SET @Row = @Row + 1
END
GO
If I'm setting up SQL DB maintenance jobs for an SL installation, I typically schedule weekly indexing when there is minimal accounting activity. The default value for SQL is to automatically rebuild indexes after 10% growth in the database size, but that would be a long time for most SL accounting databases.
The MSDN web site has a number of discussions re: SQL log management. If using a Simple recovery model, logs typically won't grow unless a DB Maintenance task is run. If using a Full Recovery model, either a SQL backup job or a backup program's SQL agent should handle log truncation of committed transactions. Checking log file sizes should be done following an SL upgrade or service pack installation in either case.
A large DB log file would affect performance if it continued growing unchecked, where it began to impact non-SQL operations and cause fragmentation of the log file itself. Except when applying DB updates, SL functions rarely lead to large log files.
I have not seen recommendations for shrinking a database, only for shrinking a log file which has grown larger than desired. Shrinking the data files of a database will lead to internal fragmentation of the tables, which will negatively affect performance.
How often would you recommend rebuilding/reorgan indexes and how often to rebuild stats for Dynamics?
Ah okay. so regular hourly (for example) transaction log backups is the rule.
but that issue is tied to bad sql planning and lack of scheduled jobs.
I see MANY folks recommending SHRINKDB to fix performance issues and I did not understand how it was related.
I'm a DBA, this is my life. The suggestions for those fixes were very confusing.
The SQL Log (.ldf) files contain the Transaction Logs for the database (.mdf). If the databases are not getting backup up regularly, and the TLogs are not getting truncated as part of a SQL DB Maintenance Plan, they will continue to grow, eat up HD space, affect performance, and eventually shut down the ability to record additional transactions.
Can you explain what you mean about the SQL log files getting too large?
how would that cause perf issues?
I prefer the Sql Server way. This link can help you, specially Define the Index Tasks and Define the Update Statistics Task
What about the database maintenance?
Did you have rebuilded o reorganized the database indexes?
Did you have used the sql profiler to know if you get locks?
Regards.
Sounds like you need to evaluate the memory, HD space and resources available with your SQL Server. A majority of the bottlenecks I see are related to an overtaxed SQL Server.
You should also check to see that the SQL Log files have not grown too large, which would cause performance issues.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (