Web app vulnerability scanners typically use a combination of active and passive vulnerability detection, with the majority of the testing done actively. For example, if the scanner requests a page and it returns a cookie without the 'secure' flag that is essentially a passive test. But if the scanner is trying to find an XSS vulnerability in a parameter it is probably going to actively send several hundred different requests to test attack variations. That's why active testing is more prominent.
Some scanners can be put into passive-only mode where they crawl through the app normally or monitor you using the app to detect vulnerabilties. As you might expect, this has less impact on the app (and is less detectable) but produces fewer results.
The payloads of attacks are generally non-malicious, but some of that depends on how the app reacts to it. I've seen poorly designed web apps crash when they receive unexpected data. To test something like an XSS attack the scanner may just try to inject basic script tags with an alert to see if it works. They normally aren't going to inject a keystroke logger, fake login form, etc.
With regards to your SQL injection question they shouldn't typically use SQL commands that delete data or change configurations, but that may depend on the scanning policy you use. Some scanners have malicious or DoS policies that might be more disruptive. It's also possible that your database will have a lot of records added by the scanner even if none of the previous data was manipulated.