May 24, 2017 at 1:53 am
Sergiy - Wednesday, May 24, 2017 12:10 AMDefault max - means "take everything".You need to define how much memory you need for OS, Web server, SSIS and allow the remaining memory for SQL Server to take.
I understand that, and that is what i am not able to identify. Because we have to consider current as well as future conditions also. Hence i have asked question.
May 24, 2017 at 2:18 am
Look at how much memory things other than SQL are currently taking.
Calculate (server memory) - (allocation for OS) - (what other things are using) = starting value for max server memory setting for SQL.
The wait stats you posted are probably not useful, as they're since SQL started. Anything more than a day and there's too much noise. Can you query syy.dm_os_wait_stats on an hourly interval (removing the unimportant waits), and calculate what the wait times are for busy hours of the day and post those?
And you're probably going to have to tune queries to get performance to a decent level. More memory is always good, but tuning queries and indexes gets the best gain.
With some very simple changes, I got a query that was running in 6.2 seconds to run in 12ms last week
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2017 at 8:32 am
I agree with everyone here. You will want to set a max for the memory. We had ours set to what we thought was good, then got some big SSIS packages in there and if the memory got too low, a 5 minute SSIS package took 7 hours to complete.
Make sure SSIS has enough memory to operate or you will regret it in the future.
Also, make sure not to set the max memory below 1.5 GB (I think it is actually 1 GB, but it is good to have extra wiggle room). I set one to 800 MB as that is all it was using after being up for a few weeks and then tried to connect via SSMS and it failed to connect. That was a stupid mistake on my part, but one I learned from.
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.
May 24, 2017 at 5:05 pm
GilaMonster - Wednesday, May 24, 2017 2:18 AMLook at how much memory things other than SQL are currently taking.
Calculate (server memory) - (allocation for OS) - (what other things are using) = starting value for max server memory setting for SQL.The wait stats you posted are probably not useful, as they're since SQL started. Anything more than a day and there's too much noise. Can you query syy.dm_os_wait_stats on an hourly interval (removing the unimportant waits), and calculate what the wait times are for busy hours of the day and post those?
And you're probably going to have to tune queries to get performance to a decent level. More memory is always good, but tuning queries and indexes gets the best gain.
With some very simple changes, I got a query that was running in 6.2 seconds to run in 12ms last week
With some very simple changes, I got a query that was running in 6.2 seconds to run in 12ms last week - Can you please put those tips here please if that is okay.
And i am collecting the data which is taking the querying the table, but currently, i have set that on friday and monday where there is too much of load on the server.
May 24, 2017 at 6:28 pm
Can you please put those tips here please if that is okay.
There are several books filled with those tips.
Which one do you want to put in this thread? 🙂
You can google "querying MS SQL" and find MS exams preparation kits - they have quite useful tips.
But those are long term solutions.
A quick one - hire a consultant.
What rate can you offer?
_____________
Code for TallyGenerator
May 24, 2017 at 10:39 pm
Sergiy - Wednesday, May 24, 2017 6:28 PMCan you please put those tips here please if that is okay.
There are several books filled with those tips.Which one do you want to put in this thread? :-)You can google "querying MS SQL" and find MS exams preparation kits - they have quite useful tips.But those are long term solutions.A quick one - hire a consultant.What rate can you offer?
Sorry Man, its not my job to engage a consultant. But I will surely keep you in mind in case there is a need for the same to do it.
I was asking because if there is something which i can do to improve my skills and queries 🙂
Thanks anyways will you keep you posted.
May 25, 2017 at 2:12 am
parth83.rawal - Wednesday, May 24, 2017 5:05 PMWith some very simple changes, I got a query that was running in 6.2 seconds to run in 12ms last week - Can you please put those tips here please if that is okay.
You want me to write a book on query tuning, just for you?
I've written a lot about SQL, try google. Also, Grant has written an book on Query Tuning, check Amazon.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2017 at 2:24 am
parth83.rawal - Wednesday, May 24, 2017 10:39 PMSorry Man, its not my job to engage a consultant. But I will surely keep you in mind in case there is a need for the same to do it.
I was asking because if there is something which i can do to improve my skills and queries 🙂
There's lots you can do. Read articles, practice what they show, buy books, try out what they show.
btw, the tuned query I mentioned was for a client. They'd had a serious performance problem for months, couldn't figure it out. I found the root causes in under a day. If it's an urgent problem, speak to your managers and see if they're willing to get someone external in to help out.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2017 at 5:24 am
parth83.rawal - Wednesday, May 24, 2017 10:39 PMSorry Man, its not my job to engage a consultant. But I will surely keep you in mind in case there is a need for the same to do it.
I was asking because if there is something which i can do to improve my skills and queries 🙂
Thanks anyways will you keep you posted.
I've got two books on how to do query tuning. One is focused on gathering query metrics, and a whole bunch of chapters on how to tune indexes, queries, etc., filled with that list of tips you want. The second is all about reading execution plans so that you can understand where and how to apply the tips from the other book. You can see the links just below this in my signature.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 25, 2017 at 11:07 am
bmg002 - Wednesday, May 24, 2017 8:32 AM...then got some big SSIS packages in there and if the memory got too low, a 5 minute SSIS package took 7 hours to complete.Make sure SSIS has enough memory to operate or you will regret it in the future.
BWAAA-HAAA!!! It never fails. Every time I think "Today is a good day to start teaching myself SSIS", I run across something like this. Today, I'll study something else, instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2017 at 11:23 am
Jeff Moden - Thursday, May 25, 2017 11:07 AMbmg002 - Wednesday, May 24, 2017 8:32 AM...then got some big SSIS packages in there and if the memory got too low, a 5 minute SSIS package took 7 hours to complete.Make sure SSIS has enough memory to operate or you will regret it in the future.
BWAAA-HAAA!!! It never fails. Every time I think "Today is a good day to start teaching myself SSIS", I run across something like this. Today, I'll study something else, instead.
The trick I have learned - keep your SSIS packages small. Which is good advice even for developing them. Once they get large enough, visual studio gets unhappy about opening them too. I try to keep SSIS just for data load and small transforms. Larger transforms end up in a T-SQL step of the job. But you start moving across 100's of tables, you need enough free memory on the server for each of them and it can get messy. Do 1 ssis package per table and it uses a LOT less memory, but your job will look like a mess and any job step fails (say step 3 out of 100), you have 97 tables that won't be updated.
So finding the happy medium there is what keeps me (and the end users) happy.
That is also a worst case scenario type thing with SSIS. The company I work for used SSIS packages before I got here and rarely had any problems. Then I come in, set up a SQL 2012 server with an SSIS catalog and help build our data warehouse and we start having these issues. BUT I learned and helped fix the issues. I do quite enjoy SSIS. Especially the SSIS catalog in 2012 (which is improved in 2016). It is a nice "set and forget" type thing. I can update an existing SSIS package from a developer (non-me) without having to even glance at the configuration for it as all of the existing ones use an Environment. So in the future if we change servers (test or live), I update a few environments and all of the SSIS packages are automatically pointing to the correct servers. No longer need to manually update 100 SSIS packages because we renamed our DEVELOPMENT environment to DEV01 or something like that.
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.
May 25, 2017 at 11:38 am
bmg002 - Thursday, May 25, 2017 11:23 AMJeff Moden - Thursday, May 25, 2017 11:07 AMbmg002 - Wednesday, May 24, 2017 8:32 AM...then got some big SSIS packages in there and if the memory got too low, a 5 minute SSIS package took 7 hours to complete.Make sure SSIS has enough memory to operate or you will regret it in the future.
BWAAA-HAAA!!! It never fails. Every time I think "Today is a good day to start teaching myself SSIS", I run across something like this. Today, I'll study something else, instead.
The trick I have learned - keep your SSIS packages small. Which is good advice even for developing them. Once they get large enough, visual studio gets unhappy about opening them too. I try to keep SSIS just for data load and small transforms. Larger transforms end up in a T-SQL step of the job. But you start moving across 100's of tables, you need enough free memory on the server for each of them and it can get messy. Do 1 ssis package per table and it uses a LOT less memory, but your job will look like a mess and any job step fails (say step 3 out of 100), you have 97 tables that won't be updated.
So finding the happy medium there is what keeps me (and the end users) happy.That is also a worst case scenario type thing with SSIS. The company I work for used SSIS packages before I got here and rarely had any problems. Then I come in, set up a SQL 2012 server with an SSIS catalog and help build our data warehouse and we start having these issues. BUT I learned and helped fix the issues. I do quite enjoy SSIS. Especially the SSIS catalog in 2012 (which is improved in 2016). It is a nice "set and forget" type thing. I can update an existing SSIS package from a developer (non-me) without having to even glance at the configuration for it as all of the existing ones use an Environment. So in the future if we change servers (test or live), I update a few environments and all of the SSIS packages are automatically pointing to the correct servers. No longer need to manually update 100 SSIS packages because we renamed our DEVELOPMENT environment to DEV01 or something like that.
Understood and thanks for the encouragement/tips. Despite all of the ETL and other things that I've done that a lot of people use SSIS for, I've never had a problem doing it all without SSIS. About the most useful thing I've seen in SSIS that's a wee bit more difficult to do using only T-SQL is parallel processing and I've still been able to do even that. Just to be absolutely clear so no one takes me the wrong way, I'm certainly not bad mouthing anyone that has found joy in the tool but I've personally found no compelling reason to use SSIS myself.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2017 at 12:18 pm
Jeff Moden - Thursday, May 25, 2017 11:38 AMUnderstood and thanks for the encouragement/tips. Despite all of the ETL and other things that I've done that a lot of people use SSIS for, I've never had a problem doing it all without SSIS. About the most useful thing I've seen in SSIS that's a wee bit more difficult to do using only T-SQL is parallel processing and I've still been able to do even that. Just to be absolutely clear so no one takes me the wrong way, I'm certainly not bad mouthing anyone that has found joy in the tool but I've personally found no compelling reason to use SSIS myself.
The main reason my company went with SSIS is that my boss doesn't like linked servers. So if we need to move data from ServerA to ServerB, our options are limited.
We do use linked servers in some cases, but we are trying to replace them with either SSIS (if we can live with non-real-time data) or with service broker (results in data duplication, but near real-time data).
We have some reports that run against our financial database and the financial software we have in place is a bit special... it isn't designed for how much data we have in there. So it is not very fast when it runs normally, adding reports pulling data out causes blocking and we get a lot more compalints from end users (both report and financial). Introduce service broker and suddenly both the reporting users and financial users are happy. They get near real time data without much impact on the financial system. Downside is we get tens of thousands of service broker messages per day which has a very slight impact on the financial system.
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.
May 25, 2017 at 1:16 pm
Jeff Moden - Thursday, May 25, 2017 11:07 AMbmg002 - Wednesday, May 24, 2017 8:32 AM...then got some big SSIS packages in there and if the memory got too low, a 5 minute SSIS package took 7 hours to complete.Make sure SSIS has enough memory to operate or you will regret it in the future.
BWAAA-HAAA!!! It never fails. Every time I think "Today is a good day to start teaching myself SSIS", I run across something like this. Today, I'll study something else, instead.
/shrug
There are plenty of posts on this forum alone from people who have come across something in TSQL that was performing terribly for any number of design/development/config reasons, use the tool wrong and it performs badly.
May 25, 2017 at 3:14 pm
The main reason my company went with SSIS is that my boss doesn't like linked servers.
Use OPENQUERY, OPENROWSET, etc.
Same connection using the same driver.
Except - it's only 1 connection from one SQL Server instance to another, instead of 2 - from SSIS to each of instances.
And you don't need an extra server (SSIS) eating into your memory and other resources.
_____________
Code for TallyGenerator
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply