<div>
I know it's probably not the right answer, but my first thought is &quot;virtualize this machine&quot;. My second thought, is has it been tested to work on a newer operating system and/or SQL version? It is probably highly suggested since 2005 and XP are no longer supported anyway.
</div>


<div>
Greg Besso: Actually, this *is* on a virtual machine. I didn't go into that in my original post because I've already been through all the optimization issues related to that platform and everything works faster than before ... except queries. <br />
<br />
No, this application will not work well on a newer platform. It is a legacy accounting system targeted to XP. We have moved most, but no all, of the functions from that machine to the new Java/html/SQLServer 2014 platform I mentioned. This is an effort to take down the very old physical hardware, virtualize, and have it runnable for migration, remaining features, and comparison with new platform.<br />
<br />
There is something about the new platform that is causing SQL Server to timeout. I need some way of analyzing this. One thing I could try is increasing the query timeout for the whole Access project -- at least to get something working, but I don't know how to do that.
</div>


<div>
ah ok. is it the same xp and sql build/versions, and is system the same quantity of cpu cores/etc? can something in the sql instance be not identical from the previous instance such as collation or other setting?
</div>


<div>
stupid question from me: have you rebuild your indexes?
</div>


<div>
Greg Besso: The old machine was AMD Sempron 3100+ 1.8GHz, 1 core, 2GB memory with a Benchmark of 452. The VM Host has an Intel i7 4790K 4.0GHz, 4 cores, 8GB of memory with a Benchmark of 11184 8G -- 25 times faster than the AMD. The XP guest is allocated 2 CPUs and 4G of memory, more horsepower all around. Interacting on the desktop feels much faster. <br />
<br />
Éric Moreau: As I mentioned in the OP, pretty much ALL tables are actually located on a remote SQL Server 2014 with views on the 2005 machine selecting from the 2014 server. See my example in OP. As such, the indexes should be &quot;over there&quot; on the 2014 server. This is the exact same setup as before migrating the system.<br />
<br />
Most queries work fine. I have a particular stored procedure that takes 40-ish seconds to run for some reason. There may be others. I can't figure out why. I've tried re-create all views used by the SP. I've tried running the Profiler, but that's gibberish to me -- tells me nothing. I need some way of identifying why this query takes so long.
</div>


<div>
Thanks Vitor. I'm going to try that, but first I'll have to put more memory into the host.<br />
<br />
As is, the system seems quite a bit faster than the original on the desktop, launching programs, etc. I feel that the problem is not with memory or CPU. Perhaps something up with I/O, although I don't know how to check that.<br />
<br />
Meanwhile, I'd like to try some kind of stop-gap to get what I have working for users. Most queries work. I have one stored procedure that is taking too long. I know how to lengthen the timeout for an individual query in VBA, but there might be others lurking. Does anyone have experience with Access 2003/VBA to know if there is a way to increase timeouts for the overall project? Perhaps modifying the connection string or something?<br />
<br />
More info ...<br />
<br />
When opening forms in Access Development in Design View, it seems to take an extraordinarily long time. Likewise when I click to view the VBA code. Does this give any clues?
</div>


<div>
<blockquote>
I'm going to try that, but first I'll have to put more memory into the host.
</blockquote>
Mind that a 32bit SQL Server can only address a maximum of 8GB.<br />
<br />

<blockquote>
I have one stored procedure that is taking too long.
</blockquote>
After enabling the AWE check again if the SP still taking long. When dealing with a big amount of data you'll need to provide the necessary memory otherwise it will do a lot swapping and this can justify the long time running.
</div>


<div>
Partial success!!! Since response time and all tactile/visual feedback indicates to me an overall faster system (I should have benchmarked the old one before mothballing), I reasoned that it *must* be something else. My 1st thought was to move the VM file to a faster drive. I don't have one handy but the system has a SATA Express connector supposedly capable of 10Mbits. <br />
<br />
My next thought was that since the physical SQL Server tables actually reside on a remote SQL Server 2014 host, perhaps there is an issue with network throughput. So, I changed the VM network setup from NAT to Bridged which supposedly talks directly to the NIC. Well, what do you know? That worked! No more timeouts and users can now actually use the legacy program.<br />
<br />
However, it's still not as fast as the old hardware. Probably nothing more I can do about the network speed, but I will next try bumping the memory per Vitor's suggestion. I'll post back results, but probably won't be until after the weekend.
</div>


<div>
I've added more memory and allocated 8G the XP VM. However, in System Properties it still only shows 3.5G. I'm unfamiliar with AWE. I've read a bit on it, but I don't find whether 32 bit applications can actually see beyond the 4G space recognized by the system itself. Will AWE still work or is there something else I have to do?<br />
<br />
Also, if I can use AWE, what do I set SQL Server memory usage to? The entire 8G and the OS will reserve what it needs? Or do I allocate something less to leave some memory resources for the OS?
</div>


<div>
<blockquote>
I don't find whether 32 bit applications can actually see beyond the 4G space recognized by the system itself.
</blockquote>
That really depends on the application. What I can assure is that SQL Server 32bit version can't address more than 2GB unless you enable AWE.<br />
<br />

<blockquote>
Also, if I can use AWE, what do I set SQL Server memory usage to?
</blockquote>
As you do it normally for 64bit. AWE will make SQL Server to see up to 8GB.<br />
<br />

<blockquote>
The entire 8G and the OS will reserve what it needs? &nbsp;Or do I allocate something less to leave some memory resources for the OS? 
</blockquote>
If you only have 8GB, I'll suggest you to reserve up to a maximum of 7GB for SQL Server leaving 1GB for OS.
</div>


<div>
Vitor - thanks for your patience. I've inched forward, but still have questions/issues. I've run the &quot;enable AWE&quot; sql per your instructions. Now, I'm trying to allocate min/max server memory. The Server &gt;&nbsp;Properties dialog gives me:<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/05_w19/1160946/AWE.jpg" target="_blank" title="min/max server memory (32 KB)"><img alt="min/max server memory" class="file-block lazyload" style="max-width: 540px;" data-src="https://filedb.experts-exchange.com/incoming/2017/05_w19/1160946/AWE.jpg" /></a>You see that AWE is enabled. I wanted to change MIN to 1G and max to 7G, the latter per your recommendation. The numbers show are what were in there before. Right off, I'm a bit confused as to what to type in here. The number currently in MAX, 2,147,483,647, is clearly NOT MB as the legend suggests. 7G in MB would be 7168. So, I'm confused as to what to put into these fields.<br />
<br />
I tried<br />

<pre><code class="code-1 nolinks" id="code-20-42125397-1"><span>sp_configure 'min server memory', 1024</span>
<span>RECONFIGURE</span>
<span>GO</span>
<span></span>
<span>sp_configure 'max server memory', 7168</span>
<span>go</span>
<span></span>
<span>Msg 233, Level 20, State 0, Line 0</span>
<span>A transport-level error has occurred when sending the request to the server. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)</span>
</code></pre>
But got the error shown. So, for the moment, I'm stuck trying to set max/min server memory.<br />
<br />
Finally (maybe), the site <a href="https://technet.microsoft.com/en-us/library/ms190673(v=sql.105).aspx" rel="ugc">https://technet.microsoft.com/en-us/library/ms190673(v=sql.105).aspx</a>&nbsp;says, &quot;Before enabling AWE, you must configure the Lock Pages in Memory policy. For more information, see How to: Enable the Lock Pages in Memory Option (Windows).&quot; That link, <a href="https://technet.microsoft.com/en-us/library/ms190730(v=sql.105).aspx" rel="ugc">https://technet.microsoft.com/en-us/library/ms190730(v=sql.105).aspx</a>, specifies going in Group Policy Editor and as the final step: &quot;7. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.&quot; Is this whole process necessary? If so, I don't understand the &quot;add account with privilege ...&quot; The SQL Server is run as a service, so I've no idea what 'user' it runs as.
</div>


AWE.jpg

<div>
<blockquote>
The number currently in MAX, 2,147,483,647
</blockquote>
That's the maximum SQL Server can address in any system, so 2TB. That's the default value.<br />
<br />

<blockquote>
7G in MB would be 7168. So, I'm confused as to what to put into these fields.
</blockquote>
Yes it is. But you need to restart SQL Server service to have it working.<br />
<br />

<blockquote>
A transport-level error has occurred when sending the request to the server
</blockquote>
This error usually happens when you loose connection to SQL Server. You'll need to reconnect and rerun the commands to set the configuration.<br />
<br />

<blockquote>
Is this whole process necessary? If so, I don't understand the &quot;add account with privilege ...&quot; The SQL Server is run as a service, so I've no idea what 'user' it runs as. 
</blockquote>
Yes, it is necessary. Which account do you have configured to run SQL Server service?
</div>


<div>
<blockquote>
<blockquote>
The number currently in MAX, 2,147,483,647
</blockquote>
<br />
That's the maximum SQL Server can address in any system, so 2TB. That's the default value.<br />
&nbsp;
</blockquote>
So, if it defaults to 2 TB, does that mean it will just use the max? Why do I need to set it to a specific value?<br />

<blockquote>
<br />

<blockquote>
Is this whole process necessary? If so, I don't understand the &quot;add account with privilege ...&quot; The SQL Server is run as a service, so I've no idea what 'user' it runs as. 
</blockquote>
<br />
Yes, it is necessary. Which account do you have configured to run SQL Server service? <br />

</blockquote>
No idea. How would I find that?
</div>


<div>
I've run the following:<br />

<pre><code class="code-1 nolinks" id="code-20-42125694-1"><span>DECLARE @sn NVARCHAR(128);</span>
<span>EXEC master.dbo.xp_regread</span>
<span>    'HKEY_LOCAL_MACHINE',</span>
<span>    'SYSTEM\CurrentControlSet\services\SQLAGENT$TRAVERSE105',</span>
<span>    'ObjectName',</span>
<span>    @sn OUTPUT;</span>
<span>SELECT @sn;</span>
</code></pre>
 and get <u>LocalSystem</u> returned. I've also run:<br />

<pre><code class="code-1 nolinks" id="code-20-42125694-2"><span>&gt; wmic service where &quot;name Like 'MSSQL%'&quot;</span>
<span>get Name , StartName</span>
<span>Name                 StartName</span>
<span>MSSQL$TRAVERSE105    LocalSystem</span>
<span>MSSQLServerADHelper  NT AUTHORITY\NetworkService</span>
</code></pre>
So, the anwer is &quot;LocalSystem&quot;? I have no such user configured.<br />
<br />
I've tried using LocalSystem in the Group Policy Editor and I get &quot;An object named 'LocalSystem' cannot be found. Check the selected object type ... blah blah blah ...&quot;
</div>


<div>
<blockquote>
So, if it defaults to 2 TB, does that mean it will just use the max? Why do I need to set it to a specific value?
</blockquote>
For many reasons depending on what you have installed in your box. Mainly is to reserve some memory for the OS but sometimes you may have more than one SQL Server instance installed so you need to limit the memory for each instance so they don't steal from each other. And a 3rd reason is that you might have other applications installed and you need to leave memory for those applications too.<br />
<br />

<blockquote>
No idea. How would I find that? 
</blockquote>
Usually in the SQL Server Configuration Manager or you can also check in the Services.<br />
<br />

<blockquote>
So, the anwer is &quot;LocalSystem&quot;? I have no such user configured.
</blockquote>
Yes. That's a special user that exists in every Windows installation and has high privileges and isn't recommended to be used as SQL Server service account even in old versions as SQL Server 2005 was the default user.<br />
Since the &quot;LocalSystem&quot; has high privileges it already has the Lock Pages in Memory permission so you should not need to perform this action for this user.
</div>


<div>
I don't have multiple instances of SQL Server running and the only other thing this XP does it daily Acronis backups.<br />

<blockquote>
Since the &quot;LocalSystem&quot; has high privileges it already has the Lock Pages in Memory permission so you should not need to perform this action for this user. 
</blockquote>
So, you're saying I don't need to specifically configure the Lock Pages in Memory policy, right?<br />
<br />
OK, I've set the min pages to 1024M and the max pages to 7168M, and I've rebooted the system. <br />
<br />
Is there any way for me to verify that the extra memory is being used? For example, an SQL server tool or query? It would be nice to confirm this is working.
</div>


<div>
Good. You should see immediately a positive impact in the performance.<br />
You can also check inside SQL Server how the memory is being used by running the following command:<br />

<pre><code class="code-1 nolinks" id="code-20-42128323-1"><span>DBCC MEMORYSTATUS</span>
</code></pre>
Here is more information about the command: <a href="https://support.microsoft.com/en-us/help/907877/how-to-use-the-dbcc-memorystatus-command-to-monitor-memory-usage-on-sql-server-2005" rel="ugc">https://support.microsoft.com/en-us/help/907877/how-to-use-the-dbcc-memorystatus-command-to-monitor-memory-usage-on-sql-server-2005</a>
</div>


<div>
SQL Server don't take all memory immediately. It will use it when needed.<br />
By your report it already shows that is using 57MB with the AWE allocation. That's a good sign.<br />
I guess if you run again that command today you'll see that was increased.<br />
From my side I don't have nothing more to recommend to you.
</div>


<div>
You're welcome.<br />
Did you see any performance boost today?
</div>


<div>
<div class="content wysiwyg-content">
I've migrated a legacy (XP) SQL Server 2005 application to new hardware which it supposedly much faster than the previous implementation. Indeed, GUI response is clearly faster. However, SQL Server responses continually timeout. <br />
<br />
The system was, and is running on XP with SQL Server 2005. The application is Access/VBA with SQL Server 2005 backend. The Access/VBA apps (.ade) can run locally on the XP, but more commonly they run from remote workstations and connect via network to the SQL Server host. Remote workstations are generally Windows 7.<br />
<br />
As this is a legacy application, pretty much all of the tables accessed really reside on an SQL Server 2014 host where the new app replacing this legacy one resides. The tables are accessed via views on the 2005 server. For example:<br />
<br />
CREATE view [dbo].[tblPaLaborClass] as<br />
select memberType as laborClass, description, null as ts from [dbserver\MEMBERSLINK].hpr<wbr />s.dbo.memb<wbr />erTypes<br />
<br />
The new platform was created by using Acronis Restore to copy an exact image from the old system to the new platform, so the XP version, SQL Server version, etc are supposedly identical. Even though the new system is apparently faster, yet queries via the Access/VBA app always timeout, whether local of remote. A remote query with Java takes 50 seconds to return -- which is too long for the Access/VBA app which simply gives a &quot;Timeout expired&quot; message after 30 seconds.<br />
<br />
How can I figure out where the bottleneck is? I've been working on this for days.
</div>
</div>


I've migrated a legacy (XP) SQL Server 2005 application to new hardware which it supposedly much faster than the previous implementation. Indeed, GUI response is clearly faster. However, SQL Server responses continually timeout. 

The system was, and is running on XP with SQL Server 2005. The application is Access/VBA with SQL Server 2005 backend. The Access/VBA apps (.ade) can run locally on the XP, but more commonly they run from remote workstations and connect via network to the SQL Server host. Remote workstations are generally Windows 7.

As this is a legacy application, pretty much all of the tables accessed really reside on an SQL Server 2014 host where the new app replacing this legacy one resides. The tables are accessed via views on the 2005 server. For example:

CREATE view [dbo].[tblPaLaborClass] as
select memberType as laborClass, description, null as ts from [dbserver\MEMBERSLINK].hprs.dbo.memberTypes

The new platform was created by using Acronis Restore to copy an exact image from the old system to the new platform, so the XP version, SQL Server version, etc are supposedly identical. Even though the new system is apparently faster, yet queries via the Access/VBA app always timeout, whether local of remote. A remote query with Java takes 50 seconds to return -- which is too long for the Access/VBA app which simply gives a "Timeout expired" message after 30 seconds.

How can I figure out where the bottleneck is? I've been working on this for days.

SQL Server 2005 running VERY slowly on new hardware

Microsoft Windows XP is the sixth release of the NT series of operating systems, and was the first to be marketed in a variety of editions: XP Home and XP Professional, designed for business and power users. The advanced features in XP Professional are generally disabled in Home Edition, but are there and can be activated. There were two 64-bit editions, an embedded edition and a tablet edition.

Windows XP

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.