Memory Issue With A SQL Server Express 2019 Server Database Application
-
Hi,
I have just moved a DB from an old HP laptop, to a newer seemingly faster HP laptop running SQL Express 2019.
The Database is behind a BOT I am running that is using Visual Studio 2022. So the project was updated from Visual Studio 2017 to 2022, and the DB from SQL Server Express 2017 to 2019.
You can read about some of the issues I had moving it at this post > https://www.sqlservercentral.com/forums/topic/trouble-moving-from-a-2017-sql-express-db-on-one-hp-laptop-to-another-hp-laptop
However I now have an issue where SQL statements running in Management Studio are fast, and the same Stored Procedures that are taking up to 5 minutes to run in my BOT (on same machine), run in seconds in SQL MS.
So the .NET C# code in the BOT is running very fast e.g PING tests to an API I am using to get data, or an HTTP request or regex to extract text from an HTML page I've scraped, but the Stored Procedures are taking a very long time to run e.g 5 mins, but when I run the same proc in SQL MS it takes seconds.
So I ran
EXECUTE xp_readerrorlog
And I can see my log file is full up of the following records which seem to indicate some kind of memory issue, looking at the speed of the processes spawned, marked as an issue, and then unloaded.
So for some reason, the same code running slow in the BOT connected to the DB is running fast in SQL Management Studio and the code is causing memory issues OR being affected by memory issues on this newer faster laptop.
LogDate ProcessInfo Text
2023-02-01 00:48:01.930 spid63 AppDomain 77 (Brainiac.dbo[runtime].76) created.
2023-02-01 00:48:08.490 spid36s AppDomain 77 (Brainiac.dbo[runtime].76) is marked for unload due to memory pressure.
2023-02-01 00:48:08.490 spid36s AppDomain 77 (Brainiac.dbo[runtime].76) unloaded.
2023-02-01 00:52:37.300 spid63 AppDomain 78 (Brainiac.dbo[runtime].77) created.
2023-02-01 00:52:45.620 spid32s AppDomain 78 (Brainiac.dbo[runtime].77) is marked for unload due to memory pressure.
2023-02-01 00:52:45.620 spid32s AppDomain 78 (Brainiac.dbo[runtime].77) unloaded.In Resource Monitor Memory it's running at 85-90% the top two processes being.
Microsoft Visual Studio with about 15 child processes under the tree so obviously that's the BOT running and,
SQL Server Windows NT (64) with SQL Server (SQLExpress) as the only child.
So I guess there is some memory issue somewhere I am getting that is affecting my newer HP laptop which is a good 6 years+ newer than my old HP laptop.
Everything else apart from this BOT calling Stored Procs to my SQL 2019 DB runs really fast. Turning it on takes a couple of seconds, whereas my old HP laptop takes 5 minutes+. Running browsers, loading web pages, and opening apps like SQL MS, VS 2022, MS Word, etc all take seconds on this newer laptop.
My old laptop runs Windows 8.1r, and my new one Windows 11, both 64-bit.
I have Speccy on both computers so I ran it to see the setups of both machines. Don't ask what some of the info means as I have no clue past the obvious RAM/Disk size etc.
OLD LAPTOP
CPU - AMD A8-6410 Mullins 28m Tech
RAM 7.00 GB Single Channel DDR3 @ 798MHz - Virtual 11 GB
Motherboard - Hewlertt-Packard 22CD (Socket FT3B)
Storage - 931GB Hitachi HFGST SATA Disk Service (SATA)
NEW LAPTOP
CPU - Intel Core i5 @ 2.5GHz - Tiger Lake=U 10mm Technology
RAM 4.00 GB, Virtual 14 GB
Motherboard - HP 89BC (U3E1)
Storage - 238GB NvMe KBG40ZNV256G KIOXIA (RAID (SSD))
So the new laptop does have half the RAM than the older laptop 4GB to 8GB, but I thought the VM disk swapping and newer chipset 11th Gen Intel(R) Core(TM) i5-1155G7 @ 2.50GHz 2.50 GHz, would make up for it. Also the new one has less hard drive space but then it comes with loads of cloud storage.
I am not a hardware techie, and I guess most people here aren't either so maybe I should post this somewhere else as well, any ideas where would be welcome?
However, the issue remains that Stored Procedure calls from the BOT to the SQL DB are taking much longer than when I run the same Stored Procedure in SQL MS. I have re-indexed everything when I did the move, which was a backup/restore with some re-linking of Logins to Users and some other issues that were fixed in the other post > https://www.sqlservercentral.com/forums/topic/trouble-moving-from-a-2017-sql-express-db-on-one-hp-laptop-to-another-hp-laptop
Maybe I should just use the old laptop to run the BOT and use this newer laptop to do all the coding and then transfer it over. It just seems weird as everything else on this new laptop is really fast, turning on, app loading, browsing, videos, better screen, just everything else apart from this BOT calling code to the DB.
Thanks for any help given and to be received by the way...
- This topic was modified 1 year, 10 months ago by Rob Reid-246754. Reason: Removed some weird copy n paste text issue
-
4GB is way way too low for anything that includes SQL Server and other software running on same pc.
on a new laptop I would not have chosen anything with less than 16GB - so if you can increase that memory to a bare minimum of 8, better 16 GB.
SQL 2019 also has slightly different memory requirements as does windows 11 - all adds up.
-
I'd say there is overhead for the OS with RAM, then add in a VM, and you've added more RAM overhead. SQL Server is very memory hungry, and you are likely running on very low RAM for the SQLOS to function, forget about any workload. I'd suspect memory is the cause of your issues.
-
Hi,
Yes, I think memory is the issue. The problem is this new laptop was bought as a XMAS present for me, as my parents saw how frustrated I got with my old laptop not being able to use the keys T Y or short cuts like CTRL+C etc.
I used to have to put them on any query page in some comments at the top, T t Y y, and then copy and paste them whenever writing queries, as SELECT and ORDER BY is pretty hard to write without those letters, and it took me so much longer to write anything at all, emails, message posts, stories, and anything really.
I cannot blame my parents though as I doubt they know anything about RAM, CPU, and memory issues, and I don't think they would have asked the salesperson if this laptop could run SQL Express on it when they bought it. It was a total surprise they did do it anyway and I was so chuffed when I opened the present on XMAS day.
As I am on the sick due to having Cancer and some other serious health issues I don't have much money to buy another laptop as the benefits don't stretch that far. Plus this new HP does run everything else so much faster than my old HP laptop.
Starting it up takes seconds, and running a full virus check on this laptop takes a minute or two compared to an hour on my old one. Opening apps like Word, Brave, VS 2022, Zoom, and loading web pages, etc are all very much faster and that's why I thought putting SQL on here wouldn't be a problem as everything else runs so much quicker here than my old laptop.
So I guess I might just use this new HP laptop for writing the code for the BOT as VS 2022 apps I've made run pretty quickly on this laptop, it's only when they need to link to SQL Server Express that it seems to cane all the memory.
I could just write the code and even the SQL on here, and then transfer it to my old HP to run, as the BOT runs very fast on the old laptop, so I might just do that until I become a millionaire from the Betting BOT I am writing and then I can pay for a Quantum Servers to run my BOT on!
Anyway thanks for the comments, I suspected it was a memory issue when I saw that SQL output from EXECUTE xp_readerrorlog, and I have noticed a few timeout errors in the log file the BOT produces.
I just thought that as everything else on here is so much faster to run than my old laptop, that this BOT + SQL project would run faster as well with a better Intel CPU chip. However, if memory is the issue then I cannot get around it unless maybe it is the only thing running on here at the time. I might try that to see if it makes any difference at all.
However, I think letting my old HP run the BOT until I acquire a Quantum Server is the best solution for now.
Thanks for your comments though!
Rob
-
You might try and see if SQL Edge works better. It's designed for low resource environments, whereas I think SQL Express tends to eat more RAM. LocalDB might work better, but not sure if it's more or less than Edge.
Personally, I might dig into SQLLite or another very low power SQL database if I needed that for storing information. If this is simpler data, I might consider just using file storage.
-
Hi Steve,
Problem is it's a pretty big and complicated system. It used to run on my old works fast server, they let me use it for free. When I gave up work due to illness though I had to put it on my old HP laptop and trim down some of the jobs and work it was doing.
If you can imagine a database full of tables for RACES, COURSES, RUNNERS and then potential BETS, MARKETS, PRICES (that change constantly in the API I am hooking into), plus a load of stats tables that show me the best trainers/jockeys re-calculated daily, course favourite bias, jockey course bias, and loads more stats tables, there was a lot of work going on.
It has to store current prices for markets and then a constant job runs pre-race by MS Agent, that looks for potential bets, using price data, and other formulas I have created, then those are placed in a BETS table with current price, desired price, time until race, and some de-normalised data for easy retrieval so I didn't have to JOIN to loads of tables to get Horse Name, Course Name, Market ID, Selection ID, Prices, Bet Status (Pending, Placed, Settled, Lapsed, Cancelled), and then a job running pretty much constantly to access the stored proc of BETS to place, which worked out whether to lower/raise the price depending on time left until the race, how much to bet OR to stop betting if we had made our daily target (not to be greedy), or we had lost the max allowed money for the day (don't chase losses always another day), and other Stop/Go factors, that were then sent to the BOT who tried to place any bets at the desired prices, and constantly retrieving the most up to date prices which then get saved.
Loops in the API to try and place the bet as the market constantly changes (just like a stock exchange except we are trading on whether a horse will win or lose, with high and low prices to trade, so that if both the high and low prices get taken at our differing £££ it means we make money whatever the race outcome), however, if only one spot is taken we have to constantly assess the market and which way a horse (stock), price is moving in it, up or down in price, and then decide whether to change our desired price (bid), to ensure we get 2 matched spots in the market.
So it really isn't a simple system at all and I've trimmed it down a lot from what it was doing to just one system, trading on the EXCHANGE rather than all the single WIN/PLACE/LAY (bet for horse to lose) bets that it was doing as well, along with custom accumulators (a mix of win and place only bets, or even LAY accumulators) that the API cannot accept so my code handled it instead.
Therefore I don't know if it is easy to buy more RAM for a laptop from a store, is it easy for them to swap my 4GB for 16GB+ or is there a way to have external RAM linked by USB/HDMI or is it a closed laptop stuck with 4GB, as if it is, the old HP although slow on most other apps seems to run my BOT->SQL Server Express really fast.
I do remember looking at SQL CE or SQL Lite but the thought of creating all the tables on the fly or just again with all the stored procs and functions as local client-side SQL (from what I remember) made me choose to install SQL Server Express as it had 90% of all the features I required, the big missing one being MS SQL Agent.
So the only options I really have is if possible to put more RAM into this new HP laptop or let the old HP be the donkey and it can just be my BOT machine. Doing nothing else but running my BOT all the time.
So until I make enough tax-free money from betting to pay for a server I am stuck with my old HP and new HP, plus I do have a DELL I nicked from my last job, but I don't see how I could link three laptops together to somehow make a bodgepot server like system, with one just running the BOT, one just running the SQL DB, etc.
So I would really need a server ideally but I can't afford hosting costs for a Microsoft Server + Windows + SQL Server licenses and costs, especially on the benefits I am on at the moment. A linux VPS would be a lot cheaper but the code is in .NET and the DB is MS SQL and MySQL cannot even do some of the simpler things that MS SQL can like return multiple recordsets from a stores procedure call and from what I remember when I had a WordPress DB and Debian VPS, they didn't even have support for UDF's so I don't know if that has changed or not as since leaving work I have been coding in Microsoft tech only. So until I can get a server I am stuck with 2 HP laptops, 1 new but slow with SQL, 1 old and fast with SQL & the BOT but slow with everything else, and a DELL with a big black patch on the screen so I cannot see what is going on behind it LOL.
Paper cups and strings....
-
Hmmmmm. you know I said I was going to try running the BOT with no other apps open e.g close this browser and VS 2022 so only the DB MS and BOT are running, well I just looked in the latest log file at the end and ...
01/02/2023 21:24:03 - Scraper Started at: 01/02/2023 21:15:41 - Finished at: 01/02/2023 21:24:03 - Crawl Took: 0hr 8min 22sec 159ms;
It took 8 minutes to do a full crawl, pretty fast and although
EXECUTE xp_readerrorlog
returned the same memory errors for that period of 8 minutes
LogDateProcessInfoText
2023-02-01 21:15:56.700spid57AppDomain 221 (Brainiac.dbo[runtime].220) created.
2023-02-01 21:16:03.370spid33sAppDomain 221 (Brainiac.dbo[runtime].220) is marked for unload due to memory pressure.
2023-02-01 21:16:03.380spid33sAppDomain 221 (Brainiac.dbo[runtime].220) unloaded.
2023-02-01 21:16:36.030spid57AppDomain 222 (Brainiac.dbo[runtime].221) created.
2023-02-01 21:16:40.590spid49sAppDomain 222 (Brainiac.dbo[runtime].221) is marked for unload due to memory pressure.
2023-02-01 21:16:40.590spid49sAppDomain 222 (Brainiac.dbo[runtime].221) unloaded.
2023-02-01 21:16:45.130spid57AppDomain 223 (Brainiac.dbo[runtime].222) created.
2023-02-01 21:16:49.160spid19sAppDomain 223 (Brainiac.dbo[runtime].222) is marked for unload due to memory pressure.
2023-02-01 21:16:49.170spid19sAppDomain 223 (Brainiac.dbo[runtime].222) unloaded.
2023-02-01 21:17:12.690spid57AppDomain 224 (Brainiac.dbo[runtime].223) created.
2023-02-01 21:17:17.410spid31sAppDomain 224 (Brainiac.dbo[runtime].223) is marked for unload due to memory pressure.
2023-02-01 21:17:17.410spid31sAppDomain 224 (Brainiac.dbo[runtime].223) unloaded.
2023-02-01 21:21:21.250spid57AppDomain 225 (Brainiac.dbo[runtime].224) created.
2023-02-01 21:21:25.690spid49sAppDomain 225 (Brainiac.dbo[runtime].224) is marked for unload due to memory pressure.
2023-02-01 21:21:25.690spid49sAppDomain 225 (Brainiac.dbo[runtime].224) unloaded.
2023-02-01 21:21:29.410spid57AppDomain 226 (Brainiac.dbo[runtime].225) created.
2023-02-01 21:21:35.620spid46sAppDomain 226 (Brainiac.dbo[runtime].225) is marked for unload due to memory pressure.
2023-02-01 21:21:35.620spid19sAppDomain 226 (Brainiac.dbo[runtime].225) unloaded.
2023-02-01 21:21:36.510spid57AppDomain 227 (Brainiac.dbo[runtime].226) created.
2023-02-01 21:21:41.950spid31sAppDomain 227 (Brainiac.dbo[runtime].226) is marked for unload due to memory pressure.
2023-02-01 21:21:41.950spid31sAppDomain 227 (Brainiac.dbo[runtime].226) unloaded.
2023-02-01 21:21:44.430spid57AppDomain 228 (Brainiac.dbo[runtime].227) created.
2023-02-01 21:21:49.810spid49sAppDomain 228 (Brainiac.dbo[runtime].227) is marked for unload due to memory pressure.
2023-02-01 21:21:49.810spid49sAppDomain 228 (Brainiac.dbo[runtime].227) unloaded.
2023-02-01 21:21:52.680spid57AppDomain 229 (Brainiac.dbo[runtime].228) created.
2023-02-01 21:21:58.540spid48sAppDomain 229 (Brainiac.dbo[runtime].228) is marked for unload due to memory pressure.
2023-02-01 21:21:58.540spid48sAppDomain 229 (Brainiac.dbo[runtime].228) unloaded.
2023-02-01 21:22:00.030spid57AppDomain 230 (Brainiac.dbo[runtime].229) created.
2023-02-01 21:22:05.540spid34sAppDomain 230 (Brainiac.dbo[runtime].229) is marked for unload due to memory pressure.
2023-02-01 21:22:05.540spid34sAppDomain 230 (Brainiac.dbo[runtime].229) unloaded.
2023-02-01 21:22:07.700spid57AppDomain 231 (Brainiac.dbo[runtime].230) created.
2023-02-01 21:22:14.300spid37sAppDomain 231 (Brainiac.dbo[runtime].230) is marked for unload due to memory pressure.
2023-02-01 21:22:14.300spid37sAppDomain 231 (Brainiac.dbo[runtime].230) unloaded.
2023-02-01 21:22:14.930spid57AppDomain 232 (Brainiac.dbo[runtime].231) created.
2023-02-01 21:22:20.860spid37sAppDomain 232 (Brainiac.dbo[runtime].231) is marked for unload due to memory pressure.
2023-02-01 21:22:20.860spid37sAppDomain 232 (Brainiac.dbo[runtime].231) unloaded.
2023-02-01 21:22:22.300spid57AppDomain 233 (Brainiac.dbo[runtime].232) created.
2023-02-01 21:22:28.080spid43sAppDomain 233 (Brainiac.dbo[runtime].232) is marked for unload due to memory pressure.
2023-02-01 21:22:28.080spid43sAppDomain 233 (Brainiac.dbo[runtime].232) unloaded.
2023-02-01 21:22:30.240spid57AppDomain 234 (Brainiac.dbo[runtime].233) created.
2023-02-01 21:22:36.800spid48sAppDomain 234 (Brainiac.dbo[runtime].233) is marked for unload due to memory pressure.
2023-02-01 21:22:36.800spid48sAppDomain 234 (Brainiac.dbo[runtime].233) unloaded.
2023-02-01 21:22:37.440spid57AppDomain 235 (Brainiac.dbo[runtime].234) created.
2023-02-01 21:22:42.370spid48sAppDomain 235 (Brainiac.dbo[runtime].234) is marked for unload due to memory pressure.
2023-02-01 21:22:42.370spid48sAppDomain 235 (Brainiac.dbo[runtime].234) unloaded.
2023-02-01 21:22:45.370spid57AppDomain 236 (Brainiac.dbo[runtime].235) created.
2023-02-01 21:22:50.480spid35sAppDomain 236 (Brainiac.dbo[runtime].235) is marked for unload due to memory pressure.
2023-02-01 21:22:50.480spid35sAppDomain 236 (Brainiac.dbo[runtime].235) unloaded.
2023-02-01 21:23:06.060spid57AppDomain 237 (Brainiac.dbo[runtime].236) created.
2023-02-01 21:23:11.050spid46sAppDomain 237 (Brainiac.dbo[runtime].236) is marked for unload due to memory pressure.
2023-02-01 21:23:11.050spid46sAppDomain 237 (Brainiac.dbo[runtime].236) unloaded.
2023-02-01 21:23:50.960spid57AppDomain 238 (Brainiac.dbo[runtime].237) created.
2023-02-01 21:26:25.360spid50sAppDomain 238 (Brainiac.dbo[runtime].237) is marked for unload due to memory pressure.
2023-02-01 21:26:25.370spid50sAppDomain 238 (Brainiac.dbo[runtime].237) unloaded.the BOT did all its work, whereas when I had my browser and VS open it took 4+ hours.
So I'm going to close everything else down and do another test to rule out a lucky dip, but the previous question/post still stands....
-
if you still have the old laptop you could use it to run SQL on its own - and have your app on your new laptop connect to it through your "home network".
you likely already have all that you need to connect the 2 - but if not the only extra thing you need is a simple $10 switch and cables or potentially a $30 wifi router
-
Hi,
Well at the moment I am writing the code on the new HP and then transferring it over to the old HP by using an external hard drive with loads of space.
It's crazy how slow the new HP takes to run a stored proc from the BOT, 6 minutes+ for the same proc call that I just ran the BOT on my old HP as I've been out all day and I needed it to get all the results and save them. The same stored proc call took less than a second on my old HP, less than a second compared to 6 minutes for the same proc call. Also, the BOT took 4 minutes to run, collecting data and saving every runner's finish position, and who won the race, and final odds etc.
So the old HP can run the BOT so fast even with loads of other apps open. I have SQL MS open, two VS 2017 projects, a browser with about 20 tabs open, Speccy, and a couple of File Explorer windows. So it runs really fast on my old HP.
Is it possible to buy extra RAM to add to a laptop, I know at work they gave me 32GB of RAM for my PC that had two screens and I always had loads of apps open and needed the RAM to handle all the work I was doing. They just took the back of the PC off and replaced the RAM card with a new one.
My old man was just here and I was showing him how fast the BOT ran on the old laptop and how slow it did on the new one. Although every other APP is faster than my old HP. So I thought the Intel CPU 5 chip might have been a lot faster than the old AMD A8 one.
So I am unsure whether I could buy a RAM card and have it fitted into my laptop, replacing the old one or is that not possible on a laptop?
I googled whether I could add more RAM and found this site, which has loads of SSD RAM, or RAM upgrades > https://www.crucial.com/products/memory/laptop-dram
And ran a free search app that was supposed to find my laptop and then show me which RAM was available but after the scan, I got this result.
So I am not sure whether I can upgrade the RAM if it cannot find the details of my laptop.
However, I did find this question from someone wanting to upgrade from 4GB to 8GB on an HP laptop > https://h30434.www3.hp.com/t5/Notebook-Hardware-and-Upgrade-Questions/Can-I-upgrade-from-4GB-ram-to-8GB/td-p/5962080
So it looks like it might be possible even if they don't know what laptop I have!
So thank you to anyone who has replied, and if it is possible to add RAM, if they can find my laptop (I don't know why their scanner app didn't though). let me know of any good sites that I could purchase it from. Also would PC World/Curries be able to actually do the RAM change on my laptop for me if I bought a new RAM card or external portable SDD RAM, shown on that site?
Thanks!
-
you will need to find your model either someowhere on the outside or potentially by using a tool like cpu-z which can sometimes identify what it it.
see https://www.hp.com/sg-en/shop/tech-takes/post/how-to-find-hp-laptop-model-number#:~:text=The%20most%20common%20location%20to,or%20behind%20an%20access%20panel. for some options
this link may also allow you to identify your product - https://support.hp.com/ie-en/drivers/laptops
once you have it it should be easy to see what type of memory it can have and if it is easy (or impossible) to upgrade
-
Cheers for those links will check them out when I get a spare minute, oh the joys of trying to move a big system that used to run on a fast server for free at my old company to laptops that I have (3, 2 HP, 1 Dell I took from Work to keep), and I could string them all together like kids with paper cups and string to talk to each other, to try and make a bodge type server where each laptop is doing a part of the process and they are all connected together either by the network in my flat or maybe cables it would be a nightmare to run.
At the moment my old HP is still fast as fxxk running the BOT, no speed or memory issues, just the problem of the letters T and Y not working and short cuts suddenly not working like CTRL+C/V/X etc, I don't know why that has suddenly happened. Maybe there is a setting that needs turning back on that I turned off and I have no idea why 2 letters which I need badly for writing SQL have stopped working.
Anyway, the old HP just ripples through 35 race cards with between 3 and 28 runners on each so fast. It has to save runner details (jockey, trainer, horse name, ratring, form etc), as well as the race details, race name, course, time, class, going (ground conditions), distance in Miles, Furlongs, and Yards, I have no idea what a yard is, I think 3 yards in a metre, and then a furlong is 220 yards off the top of my head, but I have UDF's to convert say a distance of 2m 3f 178yd into just yards, or metres, or back the other way as sometimes, especially in French or European races the distances are all shown in just metres, so I have to convert that into a Distance value of Miles, Furlongs, and Yards, as well as just a yards value. It really can be a pain with different countries all using different ways to measure stuff.
That's why I love UDF's so much to basically having a function that can be used as a column is great, I don't know if the LINUX / MySQL DB has caught up with MS SQL and supports UDFs and Stored Procedures but they didn't back when I was working with them and WordPress DBs, oh the messy code behind WordPress and some of the plugins. I spoke to a WordPress developer once and we chatted and he admitted the system has got so big, it needs a rewrite from scratch but as it's open source they cannot get enough good developers to do such a big job.
That's why all their updates are just superficial, look n feel type updates, no big changes to the code base. I gave them a DB class that I edited to fix a common problem of "MySQL DB has gone away" - basically a connection drop error, so I had a loop so that if the connection had dropped, it tried reconnecting so many times before then erroring which actually fixed the problem. I think the class is on my blog somewhere. It really is a horrible mess of a system written in PHP by so many different people for free that some classes are good, others are a nightmare with loops that call the same function e.g to get the permalink url structure, each time in a loop iteration when it could have been got before the loop, stored in a string variable and justr re-used in the loops. So it is a big job to rewrite such a big well known and free system, especially when all the developers are not being paid due to it being open source.
Anyway cheers for the advice and links!
-
I had an SP that like you, ran in seconds in SSMS but took minutes from my app. Adding SET ARITHABORT ON to the SP fixed the problem. It's set to ON when running queries in SSMS, but wasn't being set to ON from the app.
-
Hi Ryan,
There are a lot of stored procs that are called by the app.
Should I edit my data class to append "SET ARITHABORT ON" to all calls to stored procedures or should I go into every single stored procedure and add "SET ARITHABORT ON" to the top of them, I could use some SQL RegEx CLR stored procs to find the SET NOCOUNT setting and add it before that on all stored procs. OR as I said earlier I could ensure all app called SQL is appended with "SET ARITHABORT ON; " + sql in front of it.
Or is there a better way to ensure no difference between app called stored procs and SMS called procs? Like a global DB/SERVER setting I could turn on? I could look it up and I probably should I am just being lazy here as my old AMD chipset 8GB HP is running my BOT so fast. It's just all the out-of-the-blue PC crashes and other apps draining resources that do me in!
Thanks for your help anyway.
Rob
-
- Look at the execution plans for proc calls made by the bot vs running them in SSMS. There could be parameter sniffing issues generating bad plans. Or if they are not generated consistently by the app, they may not be reusing plans.
- Look at waits. It may be that the high rate of requests is overwhelming the IO capabilities of the laptop or the limits of SQL Express.
- Look for locking/blocking. Requests may be interfering with each other.As to setting options like ARITHABORT or NOCOUNT, I would do it in the stored procedures.
-
TBH Like everyone else I thinks its just a low RAM, memory issue as this laptop is 4GB and my old one is 8GB, and all the error messages in the SQL ERR Log suggest that as well.
It's just a pain coding on this laptop then copying it across to the other one, which is partial to sudden freezes, which mean hard reboots. I'd love to get extra RAM into this laptop but not sure which RAM is supported as if you look up y0u should see that some sites could not even work out what machine I had for some reason.
Anyway if that was going to be a quick fix I would have done it and I have added the SET line to the top of the main stored procs it calls.
Going into query plan caching and re-use is not what I want to spent tonight doing, some joins on those DMVs can be a nightmare if I remember. I had to give a talk to SQL newbies on query plans, forced paramaterization and the difference between building complex queries up on the front end compared to parameterized stored procs and all that malarky that went right over everyones heads like an airplane.
If I feel like wasting a few hours I might delve into it but i am pretty sure it all comes to a basic RAM / not having enough memory on this newer HP with less RAM laptop issue.
Thanks anyway
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply