Why is my database application running so slowly?

There are many reasons that a database application can be slow.  In large ERP systems like Dynamics GP, there are often complex operations happening behind the scenes when batches are being generated or posted.  All too often, we throw more hardware at a performance problem., when the issue could be as simple as a poor join or missing index.

So how do we figure out what to do about a performance problem?

First, I would make sure the SQL server is set up in a manner that follows best practices.  Here are some links to posts on setup:
Why you should not store user data on a sql server.
SQL Server setup and maintenance tips
Dream SQL Install
Creating a good set of SQL maintenance plans

If SQL is installed in a virtual environment, start here:
Keys to SQL on VMWare
Essential Guide to SQL Server Virtualization

Let's say I have done all that and I still have performance issues with posting a large batch.  The users normally use the application on a terminal server, so I am not sure if this is a network, terminal server, or application issue.

If you have a good tool, such as LiteSpeed, turn it on and post the batch.  In other words, use your tool to figure out why it is slow and make changes to fix it.  If you still have issues, or if you only have the tools that install with SQL server, here is what I would do:
1. Get a base line
- Restore the production database to a test database on the same server
- Post the batch from the terminal server while running a SQL Profiler trace
   * Time the process
   * Save the trace
- Restore prod to test (so we keep using same data set)
2. To see if terminal server is the issue, run the process from a fat client while running a trace

   * Time the process
   * Save the trace
- Restore prod to test (so we keep using same data set
3. To rule out network issues, run the process from the SQL server while running a trace

    * Time the process

   * Save the trace
- Restore prod to test (so we keep using same data set

At this point you will know whether it is a network issue, terminal server issue, or SQL issue, based on the difference in run times.
. 

If it is a SQL issue:

- Use trace results to identify the SQL objects involved and manually generate a list of those objects.

   * Tables: Check row count and explore options for purging or archiving records on large tables. Check index fragmentation.

   * Views: Check execution plan for missing indexes.  Eliminate poor joins.  Eliminate function calls that can be easily handled with inline statements.  Ensure that schema (eg. dbo) is referenced on every table operation to allow caching of the execution plan.

   * Stored Procedures: Check execution plan for missing indexes.  Eliminate poor joins.  Eliminate function calls that can be easily handled with inline statements.  Ensure that schema (eg. dbo) is referenced on every table operation to allow caching of the execution plan.  Try switching between #temp and @temp variables where used.  Look for opportunities to reduce the size of local or temp table record sets early in the processing.  Eliminate cursors where possible.

After all that, now it is time to look deeper in SQL.  
Check the performance counters for PLE and RAM in use to help determine if more RAM is needed. A good rule of thumb is that if PLE regularly falls below (75*(GB of RAM)), more RAM is needed.

Check the performance counters for disk reads and writes per second to help determine if a faster hard drive is needed.

A

 This should get you started.  I will add to this post as I find tricks that help.

Why don't any images display on my ASPX page?

Over the years, I have had more than one occasion to wonder what planet the developers of IIS are from.  It seems that every version of IIS has had at least one default setting that made no sense at all... probably not even on their planet.

Today installed an aspx application on a shiny new Windows 2008 R2 / IIS 7.5 environment.

When I ran the application, all the image tags were broken.  I verified that the images were in the correct file location and that IUSR had read access to that folder.  So why would NONE of the images display?

From the people who brought you Windows ME:
The answer is another poor decision by some IIS developer.  By default, the static content role is not installed.  Installing it fixes the issue.



I hereby issue a challenge to anyone who wishes to take it.  Go try to find a single web site running on IIS that does not have at least one image AND does not use CSS.  You will fail, because there isn't one... anywhere... I bet you would have trouble faking one.  The whole point of a website is to render content, not just text.

So I am left to ponder the extreme depth of this obtuseness.... and dream of visiting this other planet.

Did my trigger fire from and update, insert, or delete?

I recently got a question on a trigger and I could see that the coder was trying to handle two operations in a single trigger and perform separate actions based on the operation.

Now, we all know that we could create a separate trigger for each operation.  However, it is sometimes helpful for maintaining code if we use a single trigger.


Here is a simple example of how to handle the different operations in a single trigger.

create table test(
myfield varchar(1)
)
go

create  TRIGGER cstr_test
ON test
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
if exists(select 1 from deleted)
begin
if exists(select 1 from inserted)
begin
print 'update'
end
else
begin
print 'delete'
end
end
else
begin
print 'insert'

end
END
go


insert into test select 1

update test set myfield=2 where myfield=1

delete from test



I hope this helps someone!

Add items to list object in Dex using VBA

Let's say you have a drop down list box on your Dynamics GP window and you want to add an item to it.  Unfortunately, the list items are being set by dexterity code when the window opens, so this seems impossible.

However, using a little VBA, we can add our own items to the list.

We do this using SanScript!

Here is an example of how to accomplish this:

Private Sub Window_BeforeOpen(OpenVisible As Boolean)

 Dim CompilerApp As Object

    Dim CompilerMessage As String

    Dim CompilerError As Integer

    Dim CompilerCommand As String

    ' Create link without having reference marked

    Set CompilerApp = CreateObject("Dynamics.Application")

    CompilerCommand = ""

    CompilerCommand = CompilerCommand & "add item str(2099) to 'Year' of window MyWindow of form MyForm;"

    ' Execute SanScript

    CompilerApp.CurrentProductID = 131

    CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"

    CompilerError = CompilerApp.ExecuteSanscript(CompilerCommand, CompilerMessage)

    If CompilerError <> 0 Then

        MsgBox CompilerMessage

    End If

End Sub

script to move a database

This script will move the data and log files for a single database.
You need to set the database name and new paths before running.


--SCRIPT TO MOVE INDIVIDUAL DATABASE FILES

DECLARE @DATABASE_NAME VARCHAR(255),
@NEWLOGPATH VARCHAR(255),
@NEWDATAPATH VARCHAR(255),
@DATAGROWTHRATE VARCHAR(20),
@LOGGROWTHRATE VARCHAR(20)

SELECT @DATABASE_NAME='TWO',
@NEWLOGPATH='C:\SQL\LOG\',
@NEWDATAPATH='C:\SQL\DATA\',
@DATAGROWTHRATE='1024MB',
@LOGGROWTHRATE='512MB'



DECLARE @SQL VARCHAR(MAX),
@DATAFILELOGICAL VARCHAR(255),
@NEWDATAFILEPATH VARCHAR(255),
@OLDDATAFILEPATH VARCHAR(255),
@LOGFILELOGICAL VARCHAR(255),
@NEWLOGFILEPATH VARCHAR(255),
@OLDLOGFILEPATH VARCHAR(255)


SELECT @DATAFILELOGICAL='[' + RTRIM(name) + ']',
@OLDDATAFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='ROWS'

SELECT @LOGFILELOGICAL='[' + RTRIM(name) + ']',
@OLDLOGFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='LOG'

SELECT @NEWDATAFILEPATH=@NEWDATAPATH + reverse(left(reverse(@OLDDATAFILEPATH),charindex('\',reverse(@OLDDATAFILEPATH), 1) - 1))
SELECT @NEWLOGFILEPATH=@NEWLOGPATH + reverse(left(reverse(@OLDLOGFILEPATH),charindex('\',reverse(@OLDLOGFILEPATH), 1) - 1))

--SELECT @DATAFILELOGICAL,@DATAFILEPATH,@LOGFILELOGICAL,@LOGFILEPATH


SET NOCOUNT ON
SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET offline
'
PRINT @SQL
EXEC(@SQL)

IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH OR @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC sp_configure ''show advanced options'', 1;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
EXEC sp_configure ''xp_cmdshell'', 1;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)

END

IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDDATAFILEPATH + '" "' + @NEWDATAFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ', FILENAME = "' + @NEWDATAFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END

IF @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDLOGFILEPATH + '" "' + @NEWLOGFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ', FILENAME = "' + @NEWLOGFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET online
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ', 
  FILEGROWTH = ' + @DATAGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ', 
  FILEGROWTH = ' + @LOGGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + ' SET AUTO_SHRINK OFF
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)

script to move all DYNAMICS databases

I frequently have to move data and log files after GP setup to get them into the best practices configuration.  I decided to write a script to move all the common dynamics related databases to new data and log file locations.
This script assumes the system database is named DYNAMICS.
It then handles DYNAMICS, all company databases, resco, report server, smartconnect, and nodebuilder.

--script to move all DYNAMICS databases
--set the new paths and growth rates before running

DECLARE @DATABASE_NAME VARCHAR(255),
@NEWLOGPATH VARCHAR(255),
@NEWDATAPATH VARCHAR(255),
@DATAGROWTHRATE VARCHAR(20),
@LOGGROWTHRATE VARCHAR(20)

SELECT @NEWLOGPATH='C:\SQL\LOG\',
@NEWDATAPATH='C:\SQL\DATA\',
@DATAGROWTHRATE='1024MB',
@LOGGROWTHRATE='512MB'

DECLARE @SQL VARCHAR(MAX),
@DATAFILELOGICAL VARCHAR(255),
@NEWDATAFILEPATH VARCHAR(255),
@OLDDATAFILEPATH VARCHAR(255),
@LOGFILELOGICAL VARCHAR(255),
@NEWLOGFILEPATH VARCHAR(255),
@OLDLOGFILEPATH VARCHAR(255)

DECLARE C_INTERID CURSOR FOR
SELECT RTRIM(INTERID)
FROM DYNAMICS.dbo.SY01500 D
INNER JOIN sys.sysdatabases S
ON D.INTERID=S.name
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME ='DYNAMICS'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'resco%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'ReportServer%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'SmartConnect%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'NodeBuilder%'

OPEN C_INTERID
FETCH NEXT FROM C_INTERID INTO @DATABASE_NAME
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @DATAFILELOGICAL='[' + RTRIM(name) + ']',
@OLDDATAFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='ROWS'

SELECT @LOGFILELOGICAL='[' + RTRIM(name) + ']',
@OLDLOGFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='LOG'

SELECT @NEWDATAFILEPATH=@NEWDATAPATH + reverse(left(reverse(@OLDDATAFILEPATH),charindex('\',reverse(@OLDDATAFILEPATH), 1) - 1))
SELECT @NEWLOGFILEPATH=@NEWLOGPATH + reverse(left(reverse(@OLDLOGFILEPATH),charindex('\',reverse(@OLDLOGFILEPATH), 1) - 1))

--SELECT @DATAFILELOGICAL,@DATAFILEPATH,@LOGFILELOGICAL,@LOGFILEPATH


SET NOCOUNT ON
SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET offline
'
PRINT @SQL
EXEC(@SQL)

IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH OR @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC sp_configure ''show advanced options'', 1;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
EXEC sp_configure ''xp_cmdshell'', 1;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)

END

IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDDATAFILEPATH + '" "' + @NEWDATAFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ', FILENAME = "' + @NEWDATAFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END

IF @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDLOGFILEPATH + '" "' + @NEWLOGFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ', FILENAME = "' + @NEWLOGFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET online
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ', 
 FILEGROWTH = ' + @DATAGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ', 
 FILEGROWTH = ' + @LOGGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + ' SET AUTO_SHRINK OFF
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)
FETCH NEXT FROM C_INTERID INTO @DATABASE_NAME

END

CLOSE C_INTERID
DEALLOCATE C_INTERID

VB script to send an ftp file after exporting the file with smartconnect

Dim  currentdate as Date= Date.Now()
Dim  DateStamp as String=currentdate.Year.ToString() + currentdate.Month.ToString().PadLeft(2,"0") + currentdate.Day.ToString().PadLeft(2,"0")
Dim TimeStamp as String=currentdate.Hour.ToString().PadLeft(2,"0") + currentdate.Minute.ToString().PadLeft(2,"0") + currentdate.Second.ToString().PadLeft(2,"0")

Const username As String = “username”
Const password As String = “password”
Const localFile As String = “\\localshare\filename.txt"

Dim remoteFile As String = “/ftpfolder/filename_" + DateStamp + ".txt”
Dim archiveFile as String = “\\archiveshare\filename_" + DateStamp + "_" + TimeStamp  +  ".txt"


Const host As String = “ftp://username:password@ftpipaddress"
Dim operation as string
Dim buffLength As Integer = 2048
Dim buff(buffLength - 1) As Byte
Try
 If not File.Exists(localFile)   then
return false
end if

Dim URI As String = host & remoteFile

operation="create ftp web request"
Dim ftp As System.Net.FtpWebRequest = CType(System.Net.FtpWebRequest.Create(URI), System.Net.FtpWebRequest)

ftp.Credentials = New System.Net.NetworkCredential(username, password)
ftp.KeepAlive = False
ftp.UseBinary = True
ftp.Timeout = 20000
ftp.Method=System.Net.WebRequestMethods.FTP.UploadFile

operation="getting fileinfo for local file"
Dim _FileInfo As New System.IO.FileInfo(localFile)
ftp.ContentLength = _FileInfo.Length

operation="opening filestream"
Dim _FileStream As System.IO.FileStream = _FileInfo.OpenRead()

operation="getrequeststream"
Dim _Stream As System.IO.Stream = ftp.GetRequestStream()

Dim contentLen As Integer = _FileStream.Read(buff, 0, buffLength)

Do While contentLen <> 0
operation="writing stream"
_Stream.Write(buff, 0, contentLen)
contentLen = _FileStream.Read(buff, 0, buffLength)
Loop

operation="closing"
_Stream.Close()
_Stream.Dispose()
_FileStream.Close()
_FileStream.Dispose()

System.IO.File.Move(localFile,archiveFile )

return true
Catch ex As Exception
'MessageBox.Show("operation=" + operation + "  " + ex.ToString(), "Upload Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
return false
End Try